SQL Tips #3 - Searching for a keyword in a stored procedure

Similar to searching for keywords in tables definitions, it is easy to search for a keyword in a stored procedure too!

This is really useful when you're trying to find a fieldname or a particular comment in your query.

How it works

The query is very simple - it just searches the definition column in the sql_modules collection for anything that contains a string matching the query.

The definition field is actually the text of the stored procedure in its uncompiled state, so includes your query and any comments you have.

The Query

This example query looks for any stored procedure that contains a specified keyword, in this case user within its definition.

Note we're using the wildcard character (%) to match any word that contains the keyword - for example myusername will match because it contains the word user.

SELECT OBJECT_NAME(object_id) as spname
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%user%'
order by OBJECT_NAME(object_id)

The Output

The query will return the name of the stored procedure that contains the keyword specified.

sp_alterdiagram
sp_creatediagram
sp_get_user
sp_helpdiagrams
sp_renamediagram

Disclaimer

These queries are provided as a guide, and are by no means perfect. I use these on Microsoft SQL Server 2012, 2014 and 2016.

Tags