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

Question

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

User · Answer

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

User · Answer

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

User · Answer

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    lt TableName gt     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    lt StoredProcedureName gt     OBJECT      Alternatively  you can use sp depends   EXEC sp depends   lt TableName gt     Another option is to use a pretty useful tool called SQL Dependency Tracker from Red Gate

User · Answer

I wrote the code below that lists all objects where a given keyword will be found  Formally speaking it is not a true  depends on  search but it rather helps locating where a keyword is used in Stored procedures  Views  Triggers and Functions  Useful if you are using dynamic SQL   select name  type desc create date modify date  from sys all objects o inner join sys all sql modules m on m object id   o object id where definition like   tableName

User · Answer

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   gt  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

User · Answer

You can use free tool called Advanced SQL Server Dependencies http   advancedsqlserverdependencies codeplex com   It supports all database objects  tables  views  etc   and can find dependencies across multiple databases  in case of synonyms

User · Answer

The following SQL lists all object dependencies across all databases and servers   IF OBJECT ID  tempdb   Obj Dep Details   IS NOT NULL  BEGIN     DROP TABLE  Obj Dep Details END CREATE TABLE  Obj Dep Details       Database                nvarchar 128      Schema                  nvarchar 128     dependent object         nvarchar 128     dependent object type    nvarchar 60     referenced server name   nvarchar 128     referenced database name nvarchar 128     referenced schema name   nvarchar 128     referenced entity name   nvarchar 128     referenced id            int    referenced object db     nvarchar 128     referenced object type   nvarchar 60     referencing id           int    SchemaDep                nvarchar 128    EXEC sp MSForEachDB  command1  USE      INSERT INTO  Obj Dep Details SELECT DISTINCT        DB NAME                            AS  Database         SCHEMA NAME od  schema id          AS  Schema         OBJECT NAME d1 referencing id      AS dependent object        od  type desc                      AS dependent object type        COALESCE d1 referenced server name    SERVERNAME                 AS referenced server name        COALESCE d1 referenced database name  DB NAME                    AS referenced database name        COALESCE d1 referenced schema name  SCHEMA NAME ro  schema id    AS referenced schema name        d1 referenced entity name        d1 referenced id        DB NAME ro parent object id         AS referenced object db        ro  type desc                       AS referenced object type        d1 referencing id        SCHEMA NAME od  schema id           AS SchemaDep   FROM sys sql expression dependencies d1   LEFT OUTER JOIN sys all objects od     ON d1 referencing id   od  object id    LEFT OUTER JOIN sys objects ro     ON d1 referenced id   ro  object id    SELECT  Database                                        AS  Dep Object DB          Schema                                          AS  Dep Object Schema         dependent object                                 AS  Dep Object Name         LOWER REPLACE dependent object type              AS  Dep Object Type         referenced server name                           AS  Ref Object Server Name         referenced database name                         AS  Ref Object DB         referenced schema name                           AS  Ref Object Schema         referenced entity name                           AS  Ref Object Name         referenced id                                    AS  Ref Object ID         LOWER REPLACE referenced object type             AS  Ref Object Type         referencing id                                   AS  Dep Object ID    FROM  Obj Dep Details WITH NOLOCK   WHERE referenced entity name    TableName  ORDER BY  Dep Object DB            Dep Object Name            Ref Object Name            Ref Object DB    TableName Dependencies

User · Answer

There is builtin procedure to check dependents  For an example    Execute sp depends  objname N ssc RegDash RoutingAct

User · Answer

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

User · Answer

Method 1  Using sp depends   sp depends  dbo First   GO   Method 2   Using sys procedures for Stored Procedures  select Name from sys procedures where OBJECT DEFINITION OBJECT ID  like   Any Keyword Name        Any Keyword Name    is the Search keyword you are looking for  Method 3   Using sys views for Views  select Name from sys views where OBJECT DEFINITION OBJECT ID  like   Any Keyword Name        Any Keyword Name    is the Search keyword you are looking for

User · Answer

SELECT referencing schema name  referencing entity name  case when is caller dependent 0 then  NO  ELSE  Yes  END AS is caller dependent FROM sys dm sql referencing entities   Tablename    OBJECT

User · Answer

This question is old but thought I d add here   https   www simple-talk com sql t-sql-programming dependencies-and-references-in-sql-server  talks about different options pros and cons and provides stored proc  It Depends  that produces tree like result of dependencies very similar to SSMS

User · Answer

Query the sysdepends table   SELECT distinct schema name dependentObject uid  as schema          dependentObject    FROM sysdepends d  INNER JOIN sysobjects o on d id   o id  INNER JOIN sysobjects dependentObject on d depid   dependentObject id WHERE o name    TableName    A way to look just for views functions triggers procedures that reference the object  or any given text  by name is   SELECT distinct schema name so uid          so name    FROM syscomments sc   INNER JOIN  sysobjects so on sc id   so id   WHERE sc text like   Name

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

Examples related to sql-server