how can I write a query (SQL Server) that, based on a table, it can return all the tables that it references?

Question

I have a massive procedure which does a ton of deletes. The issue is that:

There are missing tables from this delete (that need to be deleted)
I'm having a hard time with the order of the deletes.

I'm wondering if there is a way to build a query in which I give it a table name and then it shows me all the tables I need to delete from before deleting this table. Anyone can give me a hand?

Try `sys.objects`... there should be a view or table for table references. — DhruvJoshi, Jun 08 '17 at 14:47
find the object_id from sys.objects then query sys.objects where the parent_object_id is that — dbajtr, Jun 08 '17 at 14:51
Sql server doesnt have a `DROP CASCADE` but this [question](https://stackoverflow.com/questions/4858488/sql-server-drop-table-cascade-equivalent) may help — Juan Carlos Oropeza, Jun 08 '17 at 14:52
You can find referenced entities with `sp_depends` or the newer `sys.dm_sql_referenced_entities` function — Panagiotis Kanavos, Jun 08 '17 at 15:03

BeanFrog · Accepted Answer · 2017-06-08T15:02:55.613

The following query returns all the tables which have foreign keys pointing into your table in @tablename.

declare @tablename sysname = 'your table name';
select OBJECT_NAME(parent_object_id) parent_object_name
from sys.foreign_keys
where OBJECT_NAME(referenced_object_id) = @tablename;

All these would have to be dropped before dropping your table.

(Tested in SQL Server 2014)

And as an additional thought - if you need to go to thenext level down, and find all foreign keys that reference this list of tables, you can use a common table expression as such:

declare @tablename sysname = 'your table name';
;with cte as (
    select OBJECT_NAME(parent_object_id) parent_object_name, OBJECT_NAME(referenced_object_id) referenced_object_name
    from sys.foreign_keys
    where OBJECT_NAME(referenced_object_id) = @tablename
    union all
    select OBJECT_NAME(parent_object_id) parent_object_name, OBJECT_NAME(referenced_object_id)
    from sys.foreign_keys fk
    inner join cte on OBJECT_NAME(fk.referenced_object_id) = cte.parent_object_name
)
select * from cte

I think this is what I was looking for. Thank you! i can accept it in 1 min. — JJ., Jun 08 '17 at 14:59

score 0 · Answer 2 · answered Jun 08 '17 at 14:48

0

shows me all the tables I need to delete from before deleting this table

Look at foreign key relationships. But if there are none defined, you're just going to have to roll up your sleeves and analyze the stored procedure.

answered Jun 08 '17 at 14:48

Xavier J

4,326
1
14
25

score 0 · Answer 3 · answered Jun 08 '17 at 15:02

One quick solution is to use the (deprecated) sp_depends stored procedure to return the dependencies of an object, eg:

exec sp_depends 'mySprocName';

A better solution is to use the sys.dm_sql_referenced_entities table-valued function to returned all referenced entities. This offers more details than sp_depends. Like all table-valued functions, it can be part of a larger query:

select distinct referenced_entity_name  
into #tables
from sys.dm_sql_referenced_entities ( 'mySproc','OBJECT')

This article shows how you can use such methods to create a PlantUML graph from such system functions and views.

how can I write a query (SQL Server) that, based on a table, it can return all the tables that it references?

3 Answers3