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%]';
-- 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]%';
-- 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]%';
-- 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]%';