Find table dependencies (Sql Server)

find-table-dependencies.sql

begin

--  Use this script to find the dependencies of tables.  
--  Set the following two variables.
--  ####################################################
    declare @TableSchema varchar(255)='Production'
    declare @TableName varchar(255)='Product'
--  ####################################################

    declare @nCnt1 int
    declare @nCnt2 int
    declare @Crsr cursor
    declare @PKTable varchar(255)
    declare @Msg varchar(255)
    
    declare @Tab table (
        [Owner] varchar(255),
        PKTable varchar(255),
        PKColumn varchar(255),
        FKOwner varchar(255),
        FKTable varchar(255),
        FKColumn varchar(255),
        Id int identity,
        Priority int)

    set nocount on

    select
        @nCnt1=1
    from
        INFORMATION_SCHEMA.TABLES
    where
        table_name=@TableName and
        table_schema=@TableSchema

    if  @@rowcount=0
    begin
        set @Msg='No table found '+@TableName
        raiserror (@Msg,16,1) with nowait
    end
        
    delete @Tab

    insert into @Tab (
        [Owner],
        PKTable,
        PKColumn)
    SELECT 
        @TableSchema,
        @TableName,
        column_name
    FROM 
        INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC INNER JOIN
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
            ON 
                TC.CONSTRAINT_TYPE = 'PRIMARY KEY' and
                TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME and 
                ku.table_name=@TableName and
                tc.table_schema=@TableSchema and
                ku.table_schema=@TableSchema
    ORDER BY 
        KU.TABLE_NAME, 
        KU.ORDINAL_POSITION

    while 1=1
    begin
        select 
            @nCnt1=Count(*)
        from
            @Tab
        insert into @Tab (
            [Owner],
            PKTable,
            PKColumn,
            FKOwner,
            FKTable,
            FKColumn)
        select 
            pk.table_schema as pkowner,
            pk.table_name as pktable,
            pk.column_name as pkcolumn,
            fk.table_schema as fkowner,
            fk.table_name as fktable,
            fk.column_name as fkcolumn
        from 
            information_schema.REFERENTIAL_CONSTRAINTS c,
            information_schema.KEY_COLUMN_USAGE fk,
            information_schema.KEY_COLUMN_USAGE pk
        where
            c.constraint_schema=fk.constraint_schema and
            c.constraint_name=fk.constraint_name and
            c.unique_constraint_schema=pk.constraint_schema and
            c.unique_constraint_name=pk.constraint_name and
            pk.ordinal_position=fk.ordinal_position and
            pk.table_name in (
                select 
                    PKTable 
                from 
                    @Tab 
                union
                select 
                    FKTable 
                from 
                    @Tab) and 
            not exists (
                select 
                    1
                from
                    @Tab t
                where
                    t.[Owner]=pk.table_schema and
                    t.PKTable=pk.table_name and
                    t.PKColumn=pk.column_name and
                    t.FKOwner=fk.table_schema and
                    t.FKTable=fk.table_name and
                    t.FKColumn=fk.column_name) and
            1=1
        order by
            pk.table_schema,
            pk.table_name,
            pk.ordinal_position
        select 
            @nCnt2=Count(*)
        from
            @Tab
        if  @nCnt1=@nCnt2
            break
    end
    update @Tab set
        [Priority]=[Id]

    update @Tab set
        [Priority]=mx.[Id]
    from
        @Tab t join 
        (
        select
            x.FKTable,
            Max(x.Id) as [Id]
        from
            @Tab x
        group by
            x.FKTable) mx
        on
            mx.FKTable=t.FKTable

    select 
        PKTable,
        PKColumn,
        FKTable,
        FKColumn,
        [Priority]
    from 
        @Tab 
    order by 
        [Priority],
        [Id]

end
go