How to Find all stored procedures referencing a table object in Database

Sometime as a database developer or DBA, we need to find all stored procedures in a database in which a specific table is being used. We can get this important information with the help of sys.objects and syscomments system objects.

Following is the query to retrieve name of all stored procedure using table object of name “Customer” in Selected Database.

IN SQL Server 2000

Select Distinct O.name, O.type_desc

from sys.objects O Join syscomments C

on O.object_id = C.id

where C.text like ‘%Customer%’

 

IN SQL Server 2005

Select O.name, O.type_desc

from sys.objects O

Where OBJECT_DEFINITION(O.object_id) like ‘%Customer%’

Enhanced by Zemanta
Advertisements

About Ashish Jain

I am Ashish Jain, a software engineer by profession. My goal of creating this blog is to share my knowledge of SQL server with all other SQL enthusiasts and also to learn from them.
This entry was posted in SQL Administration and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s