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


Wednesday, 16 March 2016

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

SQL, Programming
Share with: 

Useful SQL tips - how to list all stored procedures that have column names that match a keyword


Support this Site



Popular Articles

What is Kutamo?
Kilimanjaro 2023
Kilimanjaro 2015
Kilimanjaro 2013
Australian Postcodes
New Zealand Postcodes
Worldwide City Database

Recent Articles

Favourite Links

Kutamo Studios
ErrLog.IO
Kutamo
AfterTheCloud
Kilimanjar 2023