Database Dependencies - SQL Server

Tables¶

The following T-SQL statement will list all dependencies between tables, as indicated by their foreign key relationships.

with vwforeignkeys as (
    select distinct
         foreignKeyName = foreignKey.name
        ,onTableName = onTable.name
        ,onTableId = onTable.id
        ,againstTableName = againstTable.name 
        ,againstTableId = againstTable.id
        ,enf = case 
            when ObjectProperty(fk.constid,'CnstIsDisabled') <> 0 
                then '* NOCHECK *'
            else 'enforced'
            end
        ,del = case when ObjectProperty(fk.constid, 'CnstIsDeleteCascade') = 1  
                then 'cascade deletes'
            else ''
            end
        ,upd = case
            when ObjectProperty(fk.constid,'CnstIsUpdateCascade') = 1  
                then 'cascade updates'
            else ''
            end

    from 

        sysforeignkeys fk

        inner join sysobjects foreignKey  
            on foreignKey.id = fk.constid

        inner join sysobjects onTable 
            on fk.fkeyid = onTable.id

        inner join sysobjects againstTable  
            on fk.rkeyid = againstTable.id

    where 1=1
        AND againstTable.TYPE = 'U'
        AND onTable.TYPE = 'U'
        and againstTable.id <> onTable.id
        and onTable.name not like 'z[_]DeadCode[_]%'
        and againstTable.name not like 'z[_]DeadCode[_]%'
        and fk.fkeyid <> fk.rkeyid
    )

, cteTable as 
    (
    -- Base Case: tables depending on no other
    select distinct
        tableName = name,  
        tableId = id,  
        onTableId = 0,  
        lvl = 1,  
        rootTableId = id,  
        lineage = convert(varchar(1000),name)
    from 
        sysobjects
    where 1=1
        and type = 'U'
        and name not like 'dt[_]%'
        and name not like 'z[_]DeadCode[_]%'
        and id not in (select onTableId from vwforeignkeys)

    -- Recursive Step: tables depending on other tables
    union all select
        o.onTableName,  
        o.onTableId,  
        o.againstTableId,  
        a.lvl+1,  
        rootTableId = a.rootTableId,  
        lineage = convert(varchar(1000), a.lineage + '\' + o.onTableName)
    from
        cteTable a                    -- a = againstTable
        inner join vwforeignkeys o    -- o = onTable
            on o.againstTableId = a.tableId
    where 1=1
--        and type = 'U'
--        and name not like 'dt[_]%'
--        and name not like 'z[_]DeadCode[_]%'
    )
select 
--    tree = replicate('.',lvl) + convert(varchar(2),lvl),  
--    tableName,
--    lineage
     tableName
    ,tableLevel = max(lvl)
from  
    cteTable 
group by 
    tableName
order by 
    2,1

Code Objects - SQL Server 2005¶

Written for SQL Server 2005, the following T-SQL statement lists all code object dependencies in the database, and indicated by the sysdepends system table.

with objects as (
    -- Base Case
    select distinct
         Name           = convert(sysname, p.name)
        ,Type           = convert(sysname, p.type)
        ,Id             = convert(int, p.id)
        ,ChildName      = convert(sysname, '')
        ,ChildType      = convert(sysname, '')
        ,ChildId        = convert(int,0)
        ,Lvl            = convert(int,1)
        ,Lineage        = convert(varchar(8000),p.name)
    from 
        sysobjects p
    where 1=1
        and p.type not in ('S','IT','SQ', 'K', 'F', 'D')
        and p.id not in (select depid from sysdepends)
        and p.name not like 'dt[_]%'
        and p.name not like 'z[_]DeadCode%'
    
    -- Recursive Case
    union all select 
         Name           = convert(sysname, p.name)
        ,Type           = convert(sysname, p.type)
        ,Id             = convert(int, p.id)
        ,ChildName      = convert(sysname, c.name)
        ,ChildType      = convert(sysname, c.type)
        ,ChildId        = convert(int, c.id)
        ,Lvl            = convert(int, c.lvl+1)
        ,NamePath       = convert(varchar(8000),p.name + '\' + c.Lineage)
    from 
        sysobjects p
        inner join sysdepends d
            on p.id = d.depid
        inner join objects c
            on c.id = d.id
    where 1=1
        and c.type not in ('S','IT','SQ', 'K', 'F', 'D')
        and c.name not like 'dt[_]%'
        and c.name not like 'z[_]DeadCode%'
        and p.name not like 'dt[_]%'
        and p.name not like 'z[_]DeadCode%'
    )
select distinct
    lvl,type,name, Lineage--,childname,childtype
from 
    objects 
where 1=1
    and type not in ('U')
order by 
    lvl DESC, NAME

Code Objects - SQL Server 2000¶

Written for SQL Server 2000, the following T-SQL stored procedure lists all code object dependencies in the database, and indicated by the sysdepends system table.

ALTER PROCEDURE usp_CopyObjects 
AS

begin

declare @lIndex     int
declare @lRows      int
declare @lRowsOld   int

-- Create Temp Table to Hold Results ----------------------
CREATE TABLE #tblResults (
    lLevel int NOT NULL, 
    sType  char(2) NOT NULL,
    sName  sysname NOT NULL
    )

-- Load All Objects using a Dummy Level -------------------
insert into #tblResults (lLevel, sType, sName)
select distinct -1, type, name
from sysobjects
where not (name like 'dt[_]%')
and not (type in ('S'))

-- Update Level For Objects With No Dependencies ----------
update #tblResults 
set lLevel = 0
where sName not in (
    select distinct p.name
    from 
        sysobjects p
        inner join sysdepends d
            on p.id = d.id
        inner join sysobjects c
            on c.id = d.depid
    where 
        not (c.type  in ('S'))
        and not (p.type in ('S'))
        and not (p.name like 'dt[_]%')
        and not (c.name like 'dt[_]%')
    )

-- Determine Qty Rows With No Level Yet -------------------
set @lRowsOld = (select count(1) from #tblResults 
                 where lLevel = -1)
set @lIndex = 1

-- Loop Till We're Done -----------------------------------
while 1=1
    begin

    print 'Level   = ' + convert(varchar(2),@lIndex)
    print 'RowsOld = ' + convert(varchar(3),@lRowsOld)

/*
        select @lIndex as lngLevel, r.sName
        from #tblResults r
            inner join sysobjects p
                on p.name = r.sName
            inner join sysdepends d
                on p.id = d.id
            inner join sysobjects c
                on c.id = d.depid
            inner join #tblResults r2
                on c.name = r2.sName
        where r.lLevel = @lIndex - 1
*/

    update #tblResults 
    set lLevel = @lIndex 
    where sName in (        
        select c.Name
        from sysobjects p
            inner join sysdepends d
                on p.id = d.id
            inner join sysobjects c
                on c.id = d.depid
            inner join #tblResults r
                on c.name = r.sName
        where r.lLevel = @lIndex - 1
        )

    -- Determine Qty Rows With No Level Yet ---------------
    set @lRows = (select count(1) from #tblResults 
                                    where lLevel = -1)
    print 'Rows = ' + convert(varchar(3),@lRows)

    -- If No Rows Affected, Then We're Done ---------------
    if @lRows = @lRowsOld 
        break
    else
        continue

    -- Prep for Next Loop Iteration -----------------------
    set @lRowsOld = @lRows
    set @lIndex = @lIndex + 1

    end -- while

-- Return Records In Temp Table ---------------------------
select lLevel, sType, sName 
from #tblResults 
where sType <> 'U'
order by lLevel desc, sName

end