Must Have SSMS Queries

The links in this post contain affiliate links and I will receive a small commission if you make a purchase after clicking on my link.

Text Search for a Column Name

It may be slower because it does a free text searches in functions, stored procedures, comments, views, triggers. It may find more results than you were expecting (i.e. if the same column name is in multiple tables).

SELECT sys.objects.object_id, 
sys.schemas.name AS [Schema],
sys.objects.name AS Object_Name,
sys.objects.type_desc AS [Type]
FROM sys.sql_modules (NOLOCK)
INNER JOIN sys.objects (NOLOCK) ON sys.sql_modules.object_id = sys.objects.object_id
INNER JOIN sys.schemas (NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.sql_modules.definition COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%{ColumnName}%' ESCAPE '\'
ORDER BY sys.objects.type_desc, sys.schemas.name, sys.objects.name

Find Referenced Stored Procedures via Table and Columns

  • This uses references and is a much more direct linking.
  • It doesn't search comments.
  • If the search includes ColumnName, it will only pull it up if the column name is used in the stored procedures (i.e. Splats/Asterisks/* are not taken into consideration).
SELECT OBJECT_NAME (referencing_id),
referenced_database_name,
referenced_schema_name,
referenced_entity_name
FROM sys.sql_expression_dependencies d
WHERE OBJECT_NAME(d.referenced_id) = 'TableName'
AND OBJECT_DEFINITION (referencing_id) LIKE '%ColumnName%'
ORDER BY OBJECT_NAME(referencing_id);

Quickly view Stored Procedure text via a Query

If you have a big database, scroll and searching for a stored procedure to view the text is no fun! As long as you have the stored procedure name, you can call the stored procedure from a query.

Depending on how SSMS is setup, you can improve readability if you send Results to Text instead of Results to Grid. The shortcut key for this is Ctrl+T.

exec sp_helptext 'StoredProcedureName'

Search Stored Procedure for String

To search existing stored procedures in the current database for a specific string. If you want to search multiple databases, you'll have to run it once for each database. The ESACPE '\' is there in case you need to escape character in your search string such as square brackets.

SELECT OBJECT_NAME(object_id), 
OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%StringSearch%' ESCAPE '\'

Official Thought Worthy Logo

Can't Find What You're Looking For?

Buy Stuff
Tags
Suggested Reading