Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server Find All Stored Procedures Containing Text

May 31, 2016
Introduction:

Here I will explain how to find all stored procedures containing text in
sql server or how to search text in stored procedures in sql server or find all the stored procedures having given text or column name or table name in sql server. To get / find all the stored procedures which contains give text in sql server we need to write query with objectproperty values using syscomments table.
Description:

While working with some query I got requirement like find all the stored procedures in database which contains particular text that may be variable name or field name or some text in sql server.

To get all stored procedures which contains text in sql server we have different ways by using sql server system modules like syscomments or sys.sql_modules we can get all the stored procedures which contains particular text in sql query statements.

To get stored procedures which contains text in sql server we need to write the query like as shown below


SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS
WHERE [text] LIKE '%categorytitle%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)

When we run above query it will return all the stored procedures which contains text like “categorytitle” and output will be like as shown below


In another way by using sql_modules in sql server we can get all the stored procedures which contains particular text like as shown below.


SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%categorytitle%'

If you observe above query we are searching for “categorytitle” and in complete database to get all the stored procedures which contains in sql server. Now we will run and see the output that would be like as shown below


I hope it helps you to get all the stored procedures which contains text in sql server.

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

2 comments :

Anonymous said...

working perfectly...thanks lot

Anonymous said...

working perfectly...thanks lot

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.