Find String in Stored Procedures

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, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE CHARINDEX('OldColumnName', ROUTINE_DEFINITION) > 0
AND ROUTINE_TYPE='PROCEDURE'

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