How To Find Dependent Tables In SQL Server

Here's a query that will list all dependent tables and fields of a table you specify. That is, foreign keys and column names. This might be useful if you need to delete a record from the master table and have to delete records from its dependent tables first.

Just replace myTableName in the WHERE clause accordingly.

SELECT
pt.COLUMN_NAME AS primaryField,
c.CONSTRAINT_NAME AS keyName,
fk.TABLE_NAME AS foreignTable,
cu.COLUMN_NAME AS foreignField
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk ON
 c.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ON
 c.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON
 c.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
 SELECT tc.TABLE_NAME, kcu.COLUMN_NAME
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
 ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
 WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) pt ON pt.TABLE_NAME = pk.TABLE_NAME
WHERE pk.TABLE_NAME = 'myTableName'
ORDER BY foreignTable ASC;

OutputHere's a sample of the output you'll get:

Problem

ref: http://www.mssqltips.com/sqlservertip/1294/listing-sql-server-object-dependencies/

When developing components for SQL Server one change may affect another database object. Finding these dependent objects should be straightforward, but in most cases it is not as easy as you would think. So what is the best way to find dependency information in SQL Server?

Solution

There are several methods of getting this information. The first approach would be to use the SQL Server Management tools.
For SQL Server 2005, right click on the table name and select "View Dependencies" as shown below we are looking at dependencies for a table.

This will give you the following view so you can see objects that are dependent on the Employee table. 

And this next screen shot shows you objects that table Employee depends upon. 

To get this information, SQL Server does a lot of work to get.  To see the process that SQL Server uses to generate this data for this screen click here.

Although this is helpful to get the data via the GUI, what other approaches are there to get this data?

Method 1 - INFORMATION_SCHEMA.ROUTINES

This approach uses INFORMATION_SCHEMA.ROUTINES to search through the definition of the routine such as the stored procedure, trigger, etc...

SELECT DISTINCT routine_name, routine_type FROM INFORMATION_SCHEMA.ROUTINES WHERE         ROUTINE_DEFINITION LIKE '%UserPartnerPreference%'

Method 2 - sp_depends

This approach uses the system stored procedure sp_depends.

  
EXEC sp_depends @objname = N'yourTableName' ;

Method 3 - Using syscomments

This approach reads data from the syscomments table. This is similar to method 1.

SELECT distinct so.name 
FROM syscomments sc 
INNER JOIN sysobjects so ON sc.id = so.id 
WHERE charindex('yourTableName', text) > 0

Method 4 - sp_MSdependencies

This approach uses the system stored procedure sp_MSdependencies.

-- Value 131527 shows objects that are dependent on the specified object
EXEC sp_MSdependencies N'yourTableName', null, 1315327

-- Value 1053183 shows objects that the specified object is dependent on
EXEC sp_MSdependencies N'yourTableName', null, 1053183