Must Have SSMS Queries

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'
Official Thought Worthy Logo
0 Comments
 

Can't Find What You're Looking For?