How to indentify List of table dependencies in a SQL database (SQL 2017) ?

Question

Hello Everyone - Happy New year!!

I have listed down the list of tables and views using the Information_Schema.Tables.

Select * from Information_Schema.Tables.
where table_catalog = 'databasename'

There are about 200 tables and 30 views.

Now, I want to get the list of dependencies for these tables which got in the above query. I don't want to hard code each table name.

This is what I am looking for :

DatabaseName TableName SchemaNameof table Type (Function, StoredProcedure, View) Name of (Function, StoredProcedure, View)

Thanks

Answer 1

Hi @kkran ,

Happy New Year!

Please refer below query and check whether it is helpful to you.

use [databasename]  
go  
 
SELECT  DB_NAME() AS DatabaseName,   
d1.referenced_entity_name TableName,   
d1.referenced_schema_name SchemaName,   
case when o1.type ='P' then 'StoredProcedure'   
	 when o1.type ='TF' then 'Function'   
	 when o1.type ='V' then  'View' end Type ,   
OBJECT_NAME(d1.referencing_id) Name  
FROM sys.sql_expression_dependencies  d1   
JOIN sys.objects o   
ON  d1.referenced_id = o.[object_id]  
JOIN sys.objects p   
on OBJECT_NAME(d1.referencing_id)=p.name    
join sys.objects o1   
on OBJECT_NAME(d1.referencing_id)=o1.name     
where  o.type_desc ='USER_TABLE'  
and o1.type in ('P','TF','V')  
order by DB_NAME() ,referenced_schema_name,referenced_entity_name  

If you would like to get SQL Server Cross database Dependencies, you could also refer this forum.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

It's harder than you think. But I have an article on my web site that discusses the options: http://www.sommarskog.se/sqlutil/SearchCode.html.

Answer 3

See TechNet ScriptCenter List object dependencies (SQL Server 2008) for a T-SQL script.