CODEWITHSUNDEEP
sql-server
Axs
Axs

Reputation: 805

How to find all the dependencies of a table in 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.

Upvotes: 49

Views: 234711

Answers (13)

Mala
Mala

Reputation: 1249

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

Source: SQL Authority

Upvotes: 103

Jean-François
Jean-François

Reputation: 382

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 %'

Upvotes: 1

Ajeet Verma
Ajeet Verma

Reputation: 1123

There is builtin procedure to check dependents:
For an example ,

Execute sp_depends @objname=N'ssc.RegDash_RoutingAct'

image

Upvotes: 1

David Bunch
David Bunch

Reputation: 41

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

Upvotes: 4

Leon Bambrick
Leon Bambrick

Reputation: 26306

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%'

Upvotes: 6

Aditya Landge
Aditya Landge

Reputation: 1245

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]

Upvotes: 6

VosTi
VosTi

Reputation: 131

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.

Upvotes: 13

bjnr
bjnr

Reputation: 3437

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.

Upvotes: 15

Jack0fshad0ws
Jack0fshad0ws

Reputation: 573

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

enter image description here

Upvotes: 2

user586399
user586399

Reputation:

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]

Upvotes: 12

Deepan Kanugula
Deepan Kanugula

Reputation: 31

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');

Upvotes: 0

knyazs
knyazs

Reputation: 99

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).

Upvotes: 2

Darshit Shah
Darshit Shah

Reputation: 669

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

Upvotes: 3

Related Questions