Q&A – SQL Server Developer: How to search for the text in stored procedures

SQL Server provides different metadata views that can help developers in their work, but sometimes developers are not up to date with the latest system tables and views changes.

In this Q&A we provide the query to search for the specific text in the stored procedures, views or functions. This is the easiest way to search for the string in objects definitions:

SELECT OBJECT_NAME(OBJECT_ID) FROM sys.sql_modules 
WHERE [definition] LIKE '%my_search_string%'

Another way is using syscomments system table which was used in older versions of SQL Server:

SELECT OBJECT_NAME(ID) FROM sys.syscomments 
WHERE [text] LIKE '%my_search_string%'

Also, you can use OBJECT_DEFINITION metadata function:

SELECT name, type_desc FROM sys.all_objects 
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%my_search_string%'

Share

MSSQL Tips: Tip #8 – Using Policy Based Management to check number of SQL Server data and log files

In this tip I explain how to make sure that you have only one database transaction log file and more than one data file using policy based management.

Please read this MSSQL Tip: “Using Policy Based Management for checking SQL Server database file extensions“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share