How to find all the dependencies of a table in sql server

sql-server

I have a database where i have list of tables,procedures,views and triggers. But i want a query to get all the dependencies of a table including child tables which are referring the parent table.

The following are the ways we can use to check the dependencies:

Method 1: Using sp_depends

 sp_depends 'dbo.First'
 GO

Method 2: Using information_schema.routines

 SELECT *
 FROM information_schema.routines ISR
 WHERE CHARINDEX('dbo.First', ISR.ROUTINE_DEFINITION) > 0
 GO

Method 3: Using DMV sys.dm_sql_referencing_entities

 SELECT referencing_schema_name, referencing_entity_name,
 referencing_id, referencing_class_desc, is_caller_dependent
 FROM sys.dm_sql_referencing_entities ('dbo.First', 'OBJECT');
 GO

In SQL Server 2008 there are two new Dynamic Management Functions introduced to keep track of object dependencies: sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities:

1/ Returning the entities that refer to a given entity:

SELECT
        referencing_schema_name, referencing_entity_name, 
        referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('<TableName>', 'OBJECT')

2/ Returning entities that are referenced by an object:

SELECT
        referenced_schema_name, referenced_entity_name, referenced_minor_name, 
        referenced_class_desc, is_caller_dependent, is_ambiguous
FROM sys.dm_sql_referenced_entities ('<StoredProcedureName>', 'OBJECT');

Alternatively, you can use sp_depends:

EXEC sp_depends '<TableName>'

Another option is to use a pretty useful tool called SQL Dependency Tracker from Red Gate.

Besides the methods described in other answers (sp_depends system stored procedure, SQL Server dynamic management functions) you can also view dependencies between SQL Server objects - from SSMS.

You can use the View Dependencies option from SSMS. From the Object Explorer pane, right click on the object and from the context menu, select the View Dependencies option

I myself prefer a 3rd party dependency viewer called ApexSQL Search. It is a free add-in, which integrates into SSMS and Visual Studio for SQL object and data text search, extended property management, safe object rename, and relationship visualization.

Finding all foreign keys

SELECT src.name, srcCol.name, dst.name, dstCol.name
FROM sys.foreign_key_columns fk
    INNER JOIN sys.columns srcCol ON fk.parent_column_id = srcCol.[column_id] 
        AND fk.parent_object_id = srcCol.[object_id]
    INNER JOIN sys.tables src ON src.[object_id] = fk.parent_object_id
    INNER JOIN sys.tables dst ON dst.[object_id] = fk.[referenced_object_id]
    INNER JOIN sys.columns dstCol ON fk.referenced_column_id = dstCol.[column_id] 
        AND fk.[referenced_object_id] = dstCol.[object_id]

In SQL Server 2008 or above I use the following query to find all the dependant stored procedures, user defined functions, triggers, etc. for a given table:

SELECT 
coalesce(Referenced_server_name+'.','')+ --possible server name if cross-server
coalesce(referenced_database_name+'.','')+ --possible database name if cross-database
coalesce(referenced_schema_name+'.','')+ --likely schema name
coalesce(referenced_entity_name,'') + --very likely entity name
coalesce('.'+col_name(referenced_ID,referenced_minor_id),'')AS [referencing],
coalesce(object_schema_name(Referencing_ID)+'.','')+ --likely schema name
object_name(Referencing_ID)+ --definite entity name
coalesce('.'+col_name(referencing_ID,referencing_minor_id),'') AS [referenced]
FROM sys.sql_expression_dependencies
WHERE referenced_id =object_id('Table_name')
ORDER BY [referenced]

How to find all the dependencies of a table in sql server

Related

Recent Posts