Friday, January 23, 2009

Find String in Stored Procedures/Triggers/Jobs

The following SQL samples are very useful when searching your Database for a certain string. For example if you change a column name you may want to search Tables, Views, Procedures and Triggers throughout the Database for any references to the old column name.

1. Use the following SQL when searching for *Tables which contain the old column name:
use dbname
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE CHARINDEX('OldColumnName', COLUMN_NAME) > 0
*Note: The above example also returns Views which reference the old column.

2. Use the following SQL when searching for Stored Procedures which contain the old column name:
use dbname
SELECT ROUTINE_NAME, OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE CHARINDEX('OldColumnName', OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME))) > 0
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY ROUTINE_NAME

3. Use the following SQL when searching for Triggers which contain the old column name:
use dbname
SELECT DISTINCT sysobjects.name AS [Object Name],syscomments.text
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type = 'TR'
AND sysobjects.category = 0
AND CHARINDEX('OldColumnName',syscomments.text) > 0

4. Use the following SQL when searching for Jobs which contain a certain procedure name:
SELECT DISTINCT msdb.dbo.sysjobs.name AS [Object Name], msdb.dbo.sysjobsteps.command
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobsteps ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobsteps.job_id
WHERE msdb.dbo.sysjobsteps.command LIKE '%ProcName%'

No comments: