Wednesday, April 10, 2013

Sql Server - Search List of the stored procedure, functions and views where specific text is being used

In SQL Server, Sometimes we need to find the list of the stored procedure, functions and views where specific table or any other text is being used.

For Example -

a). Here, I want to return the list of stored procedure from my database where text/table “Customer” is being used-
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%Customer%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1

GROUP BY OBJECT_NAME(id)

b). Here, I want to return the of functions and views from my database where text/table “Customer” is being used-
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%Customer%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 0

GROUP BY OBJECT_NAME(id)

About the Author

Manish Rai

Author & Editor

I am Manish Rai an IT Professional, I started the www.einfoportal.com at the beginning of 2013.My goal is to establish www.einfoportal.com as a information portal that will help every individual(s) to get the information related to their day by day activities and use this information for the good purpose. I love to share my knowledge with the people. Follow me on Facebook | Google+ | Twitter

0 comments:

Post a Comment

 
Copyright © www.einfoportal.com . All Rights Reserved