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’

Get all the tables that contains a particular column

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

Error handling in SQL server

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

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

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;