Query for Geting dependent tables of a stored procedure

17 Dec

In MS SQL Server 2008, you may get the table names dependents by a stored procedure using the following query

SELECT name

,(SELECT name FROM sys.schemas WHERE schema_id=t.schema_id) AS schemaName

FROM  sys.tables t

WHERE  t.object_id in (SELECT depid

FROM sys.sysdepends

WHERE id In (SELECT s.object_id FROM sys.procedures s WHERE name=’SPName‘))

SPName  is the name of  stored procedure.

Th output will be the table name and its schema. Happy querying .. 🙂

Advertisements

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

%d bloggers like this: