Ravindar, Biztalk Developer and Administrator

Just another BizTalk blog

query to get all stored procedures that conatains a particular word

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ‘%foobar%’ AND ROUTINE_TYPE=’PROCEDURE’

October 12, 2010 Posted by | Sql Server | Leave a comment

Get all the tables that contains a particular column

Select table_name from information_Schema.columns where column_name=’your column name here’

October 12, 2010 Posted by | Sql Server | Leave a comment

Error handling in SQL server

http://www.simple-talk.com/sql/t-sql-programming/sql-server-error-handling-workbench/

April 29, 2010 Posted by | Sql Server | Leave a comment

split a comma seperated string in sql server

the variable AttributeItemID is the commma seperated string.

IF substring(@AttributeItemID, LEN(@AttributeItemID)-1,1)’,’
SET @AttributeItemID = @AttributeItemID + ‘,’ –add a comma to the end if it isn’t there

DECLARE @pos as int;
SET @pos=0;
DECLARE @splittedValue varchar(200);

WHILE charindex(‘,’,@AttributeItemID)>0
BEGIN
SET @splittedValue = cast(substring(@AttributeItemID,0, charindex(‘,’,@AttributeItemID))as int)

SET @AttributeItemID = substring(@AttributeItemID, charindex(‘,’,@AttributeItemID)+1, LEN(@AttributeItemID) – @pos) –remove the first item from the list
END

January 20, 2010 Posted by | Sql Server | Leave a comment

Change schema name on Tables and Stored procedures in SQL Server 2005

Change Stored procedures in sql 2005

‘Note this is the raw query
SELECT ‘ALTER SCHEMA dbo TRANSFER ‘ + s.Name + ‘.’ + p.Name FROM sys.Procedures p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = ‘CHANGE_ME_Username’

It would create this kind of output.

* ALTER
* SCHEMA dbo TRANSFER steveschofield.spAuthors1 ALTER SCHEMA dbo TRANSFER steveschofield.spAuthors2
* ALTER SCHEMA dbo TRANSFER steveschofield.spAuthors3

You would run in a new query window, after this refresh SQL Management studio
and the stored procedures would be dbo.spAuthors1, dbo.spAuthors2, dbo.spAuthors3.

Change schema for tables
ALTER SCHEMA NewSchemaname TRANSFER oldschemaname.tablename;

January 19, 2010 Posted by | Sql Server | Leave a comment