I do a lot of work in SQL Server and one of the things I dislike the most is trying to find all instances of a specific piece of text in Stored Procedures, Functions, Views, Triggers, Constraints etc.

RedGate have a fantastic free tool called SQL Search which is now on version 2 for doing all of this for you and I would definitely suggest you install version 2 of the product and use it as it’s amazing, however there may be times where you are unable to install the product or you may discover a problem with it (I’ve only recently installed version 2 of the product so am still in the honeymoon period with it).

I used to use version 1 of SQL Search all the time but found that it could be (on occasion) a bit hit and miss with it’s results.  Whilst working for one client I had to find all instances of where a UNIQUE IDENTIFIER field had been cast to a VARCHAR in a JOIN and get rid of these to resolve some very serious performance issues (NB: don’t CAST in SQL JOINS – ever) and it was during this process that I found that version 1 of SQL Search wasn’t picking up the right results.  This prompted me to shelve the product and compile a list of useful SQL commands I could use in it’s place which I have listed below.

You’ll see from the code that I’ve used the INFORMATION_SCHEMA schema in all cases but one and that’s because I just think the SQL flows better.  All (I think) the information that I’ve pulled from INFORMATION_SCHEMA can be pulled from SYS.OBJECTS and SYS.SQL_MODULES as per my Triggers example but I just don’t like the SQL as much.  It’s my personal preference but use whatever is best for you and, as always, feel free to contribute.

Searching Stored Procedures and Views

-- Search in Functions and Stored Procedures
SELECT ROUTINE_TYPE AS [Type], ROUTINE_NAME AS [Name], 'Text' AS [Matches On], ROUTINE_DEFINITION AS [Detail]
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%[INSERT FILTER TEXT HERE%]'
AND (ROUTINE_TYPE='FUNCTION' or ROUTINE_TYPE='PROCEDURE');

Searching Table Columns

-- Search in Table Columns
SELECT 'TABLE' AS [Type], TABLE_NAME AS [Name], 'Column' AS [Matches On], COLUMN_NAME AS [Detail]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%[INSERT FILTER TEXT HERE%]';

Searching Views

-- Search in Views
SELECT 'VIEW' AS [Type], TABLE_NAME AS [Name], 'Text' AS [Matches On], VIEW_DEFINITION AS [Detail]
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%[INSERT FILTER TEXT HERE]%';

Searching Constraints

-- Search in Constraints
SELECT 'CONSTRAINT' AS [Type], TABLE_NAME AS [Name], 'Name' AS [Matches On], CONSTRAINT_NAME AS [Detail]
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE CONSTRAINT_NAME LIKE '%[INSERT FILTER TEXT HERE]%';

Searching Triggers

-- Search in Triggers
SELECT Type_Desc AS [Type], Name AS [Name], 'Text' AS [Mathces On], m.definition AS [Detail]
FROM SYS.OBJECTS o
INNER JOIN SYS.SQL_MODULES m ON o.Object_Id = m.Object_Id
WHERE TYPE = 'TR' AND m.definition LIKE '%[INSERT FILTER TEXT HERE]%';