Get all Foreign Keys for All dependent tables for One Table to nth Level

Question

I have one table Called Member with Column Name Member_Id.

This Table Referred by more than 23 other tables as Primary Table with Member_Id as Foreign Column.

Now these 23 tables also have Primary Keys, and some serve also as Primary table for other tables.

So I want to fetch all Foreign Keys for all dependent Table referring to table Member.

My Goal is to Truncate Member table which has Foreign keys. I can't use Delete as these tables have more data, so it may take ages for me to delete the data.

For Example:-

Member --> Member-ID

Member-Contact connect with Member table Using Member_ID, Primary Key Contact_No Member_Population connect with Member table Using Member_ID, Primary Key population_seq_no

...23 More

These Member-Contact, Member_Population and 23 more also have dependent tables with other tables as Foreign Keys.

So before truncate I need to Drop All Foreign Keys then Truncate All these dependent tables then Restore these foreign keys.

Till now I write this query which fetch all Foreign Keys for one table

SELECT  ROW_NUMBER() Over(Order BY f.parent_object_id) as RowID,
        OBJECT_NAME(f.parent_object_id) TableName,
        COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName,
        f.name as FKConstraintName,
        COL_NAME(fc.referenced_object_id,fc.referenced_column_id) as ReferenceColName
--INTO  #temp_ReferenceContstraints     
FROM    sys.foreign_keys AS f
        INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
        INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id
WHERE   OBJECT_NAME (f.referenced_object_id) = 'Member'

I want to find all foreign keys for all dependent tables?

why you don't use user interface for all dependent tables in `sql server` ? — Khurram Ali, Mar 13 '15 at 13:12
http://blog.sqlauthority.com/2010/02/04/sql-server-get-the-list-of-object-dependencies-sp_depends-and-information_schema-routines-and-sys-dm_sql_referencing_entities/ — Khurram Ali, Mar 13 '15 at 13:20
Well you could take the results of the query you have, run a cursor over the results and run the same query over each of those tables; and you can set it up recursively so it goes to the Nth level. — Tab Alleman, Mar 13 '15 at 13:26
Ok, so instead of recursively calling the SP, put the recursive cursor in the SP, so you only call it once. Or do it in a script without any SP's. Especially if this is a one-off. — Tab Alleman, Mar 13 '15 at 13:42

score 7 · Answer 1 · edited Aug 22 '15 at 21:42

Try this:

 ;
 WITH fkey 
 as (
      select   constraint_id = f.object_id
        ,      constraint_name = f.name
        ,      parent_object_id
        ,      parent_name = object_name(f.parent_object_id)
        ,      referenced_object_id
        ,      referenced_object_name = object_name(f.referenced_object_id)
      from     sys.foreign_keys f
 )
 ,  recurse
 as (
      select   depth = 1
         ,     *
      from     fkey
      where    referenced_object_name = 'myTable'  -- <-- use this to filter results.
      union all
      select   depth = recurse.depth + 1
        ,      fkey.*
      from     fkey
         join  recurse 
            on fkey.referenced_object_id = recurse.parent_object_id
 )
 ,  recurseWithFields
 as (
      select   r.*
            ,  parent_column_id
            ,  parent_column_name = p_ac.name
            ,  referenced_column_id
            ,  reference_column_name = r_ac.name
      from     recurse r
          join sys.foreign_key_columns fc
            on r.constraint_id = fc.constraint_object_id
          join sys.all_columns p_ac
            on fc.parent_column_id = p_ac.column_id
            and fc.parent_object_id = p_ac.object_id
          join sys.all_columns r_ac
            on  fc.referenced_column_id = r_ac.column_id
            and fc.referenced_object_id = r_ac.object_id
 )
 select *
 from   recurseWithFields

If you want to get all the tables related to a particular table, you need to filter in the CTE called recurse, in the location indicated by the comment.

@Piyush I didn't do anything to handle it, but if you have any foreign keys from a table to itself, you could get some "runaway recursion". You might have to put a where clause in `fkey` to prevent it: `where parent_object_id <> referenced_object_id`. — Ann L., Mar 13 '15 at 21:49

Get all Foreign Keys for All dependent tables for One Table to nth Level

1 Answers1

Linked