获取一个表到第n级的所有从属表的所有外键 [英] Get all Foreign Keys for All dependent tables for One Table to nth Level

问题描述

我有一个名为Member的表，列名称为Member_Id.

I have one table Called Member with Column Name Member_Id.

此表被超过23个其他表称为主表，而Member_Id称为外部列".

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

现在，这23个表也具有主键，其中一些还可以用作其他表的主表.

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

所以我想获取所有依赖表Member的依赖表的所有外键.

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

我的目标是截断具有外键的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.

例如:-

会员->会员ID

Member-Contact使用Member_ID，主键Contact_No与Member表连接 Member_Population使用Member_ID，主键population_seq_no

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更多

这些Member-Contact，Member_Population和另外23个也具有从属表，其他表作为外键.

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?

推荐答案

尝试一下:

 ;
 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 

如果要获取与特定表相关的所有表，则需要在注释指示的位置过滤名为recurse的CTE.

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.

这篇关于获取一个表到第n级的所有从属表的所有外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！