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


Wednesday, 16 March 2016

Share with: 
 

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
Useful SQL tips - how to list all stored procedures that have column names that match a keyword
 
 

Popular Articles

What is Kutamo?
Kilimanjaro 2015
Exploring Lync and IoT
Exchange 2013 in 60 minutes
Monitoring Lync with MRTG
Lync UCWA Tutorial - Introduction
Tutorial Parts 1 | 2 | 3 | 4 | 5

Recent Articles

Australian Postal Codes
Skype Web SDK
Using the Skype Web SDK from any language or framework
Building a Skype for Business Auto Responder using the Skype Web SDK
Exporting Lync or Skype for Business Contacts with the Skype Web SDK

Favourite Links

Kutamo
Telco Together Foundation
Cloud on Kilimanjaro

Tags / Keywords

SQL, Programming