为什么“查看依赖关系”会这样？不显示两个数据库上的表之间的外键？

Question

我在 SQL Server Management Studio 2005 中发生了一些小但非常奇怪的事情。我有 2 个数据库目录，它们本质上是相同的。有一个 Market 表，其中包含其他几个表的外键，例如“Project”表有一个 MarketID 列，其中包含来自 dbo.Market 的 FK 引用。在两个数据库目录中，MarketID 不为空，数据类型相同（int - 显然），并且在两个数据库目录中，在两个表之间创建了一个外键（从 dbo.Market 到 dbo.Project）。

在 PRD 目录上，如果我右键单击 Market 表并从上下文菜单中选择“查看依赖关系”，我将看到 MarketId 是其他表上的外键的表（例如 dbo.Project 将出现在“依赖于 Market 的对象”下） ）。如果我在其他数据库目录（TST）上执行相同的操作，它不会显示有任何外键。 PRD 和 TST 目录托管在同一数据库服务器上，因此不是一个目录具有不同版本或 SS Management Studio SP 之类的情况......这会带来什么？

更奇怪的是，如果我在两个目录上创建数据库图表，则所有比较表的外键看起来都相同，但“查看依赖关系”的行为不同。如果我查看每个目录上表格的“索引”和“统计”，它们基本上也相同......我很困惑。

任何人都可以建议什么事情会影响当您选择“查看依赖关系”时显示的树中外键关系的可见性

最初我们认为谁删除了外键，但是当我们查看数据图或外键文件夹时他们在那里……

Answer 1

这是 SSMS 使用的 SQL Profiler 脚本。我想这只是一个运行它来看看两个数据库之间有什么不同的问题。

USE TST

CREATE TABLE #tempdep (objid int NOT NULL, objtype smallint NOT NULL);

INSERT INTO #tempdep 

SELECT
tbl.object_id AS [ID],
3
FROM
sys.tables AS tbl
WHERE
(tbl.name=N'Market' and SCHEMA_NAME(tbl.schema_id)=N'dbo');

declare @find_referencing_objects int
set @find_referencing_objects = 1
-- parameters:
-- 1. create table #tempdep (objid int NOT NULL, objtype smallint NOT NULL)
--    contains source objects
-- 2. @find_referencing_objects defines ordering
--    1 order for drop
--    0 order for script

declare @must_set_nocount_off bit
set @must_set_nocount_off = 0

IF @@OPTIONS & 512 = 0 
   set @must_set_nocount_off = 1
set nocount on

declare @u int
declare @udf int
declare @v int
declare @sp int
declare @def int
declare @rule int
declare @tr int
declare @uda int
declare @uddt int
declare @xml int
declare @udt int
declare @assm int
declare @part_sch int
declare @part_func int
declare @synonym int

set @u = 3
set @udf = 0
set @v = 2
set @sp = 4
set @def = 6
set @rule = 7
set @tr = 8
set @uda = 11
set @synonym = 12
--above 100 -> not in sys.objects
set @uddt = 101
set @xml = 102
set @udt = 103
set @assm = 1000
set @part_sch = 201
set @part_func = 202


/*
 * Create #t1 as temp object holding areas.  Columns are:
 *  object_id     - temp object id
 *  object_type    - temp object type
 *  relative_id      - parent or child object id
 *  relative_type  - parent or child object type
 *  rank  - NULL means dependencies not yet evaluated, else nonNULL.
 *   soft_link - this row should not be used to compute ordering among objects
 *   object_name - name of the temp object
 *   object_schema - name the temp object's schema (if any)
 *   relative_name - name of the relative object
 *   relative_schema - name of the relative object's schema (if any)
 *   degree - the number of relatives that the object has, will be used for computing the rank
 *   object_key - surrogate key that combines object_id and object_type
 *   relative_key - surrogate key that combines relative_id and relative_type
 */
create table #t1(
   object_id         int         NULL,
   object_type       smallint    NULL,
   relative_id       int         NULL,
   relative_type        smallint    NULL,
   rank        smallint    NULL,
   soft_link      bit      NULL,
   object_name       sysname     NULL,
   object_schema        sysname     NULL,
   relative_name     sysname     NULL,
   relative_schema      sysname     NULL,
   degree            int NULL,
   object_key bigint NULL,
   relative_key bigint NULL
)

create unique clustered index i1 on #t1(object_id, object_type, relative_id, relative_type) with IGNORE_DUP_KEY

declare @iter_no int
set @iter_no = 1

declare @rows int
set @rows = 1

declare @rowcount_ck int
set @rowcount_ck = 0

insert #t1 (relative_id, relative_type, rank) 
   select l.objid, l.objtype, @iter_no from #tempdep l

while @rows > 0
begin
   set @rows = 0
   if( 1 = @find_referencing_objects )
   begin
      --tables that reference uddts or udts (parameters that reference types are in sql_dependencies )
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, c.object_id, @u, @iter_no + 1
         from #t1 as t
         join sys.columns as c on  c.user_type_id = t.relative_id
         join sys.tables as tbl on tbl.object_id = c.object_id -- eliminate views
         where @iter_no = t.rank and (t.relative_type=@uddt OR t.relative_type=@udt)
      set @rows = @rows + @@rowcount

      --tables that reference defaults ( only default objects )
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, clmns.object_id, @u, @iter_no + 1
         from #t1 as t
         join sys.columns as clmns on clmns.default_object_id = t.relative_id
         join sys.objects as o on o.object_id = t.relative_id and 0 = isnull(o.parent_object_id, 0)
         where @iter_no = t.rank and t.relative_type = @def
      set @rows = @rows + @@rowcount

      --types that reference defaults ( only default objects )
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, tp.user_type_id, @uddt, @iter_no + 1
         from #t1 as t
         join sys.types as tp on tp.default_object_id = t.relative_id
         join sys.objects as o on o.object_id = t.relative_id and 0 = isnull(o.parent_object_id, 0)
         where @iter_no = t.rank and t.relative_type = @def
      set @rows = @rows + @@rowcount

      --tables that reference rules
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, clmns.object_id, @u, @iter_no + 1
         from #t1 as t
         join sys.columns as clmns on clmns.rule_object_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @rule
      set @rows = @rows + @@rowcount

      --types that reference rules
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, tp.user_type_id, @uddt, @iter_no + 1
         from #t1 as t
         join sys.types as tp on tp.rule_object_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @rule
      set @rows = @rows + @@rowcount

      --tables that reference XmlSchemaCollections
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, c.object_id, @u, @iter_no + 1
         from #t1 as t
         join sys.columns as c on c.xml_collection_id = t.relative_id
         join sys.tables as tbl on tbl.object_id = c.object_id -- eliminate views
         where @iter_no = t.rank and t.relative_type = @xml
      set @rows = @rows + @@rowcount

      --procedures that reference XmlSchemaCollections
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, c.object_id, case when o.type in ( 'P', 'RF', 'PC' ) then @sp else @udf end, @iter_no + 1
         from #t1 as t
         join sys.parameters as c on c.xml_collection_id = t.relative_id
         join sys.objects as o on o.object_id = c.object_id
         where @iter_no = t.rank and t.relative_type = @xml
      set @rows = @rows + @@rowcount

      --udf, sp, uda, trigger all that reference assembly
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, am.object_id, (case o.type when 'AF' then @uda when 'PC' then @sp when 'FS' then @udf when 'FT' then @udf when 'TA' then @tr else @udf end), @iter_no + 1
         from #t1 as t
         join sys.assembly_modules as am on am.assembly_id = t.relative_id
         join sys.objects as o on am.object_id = o.object_id
         where @iter_no = t.rank and t.relative_type = @assm
      set @rows = @rows + @@rowcount

      -- CLR udf, sp, uda that reference udt
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select distinct t.relative_id, 
               t.relative_type, 
               am.object_id, 
               (case o.type 
                  when 'AF' then @uda 
                  when 'PC' then @sp 
                  when 'FS' then @udf 
                  when 'FT' then @udf 
                  when 'TA' then @tr 
                  else @udf end), 
               @iter_no + 1
         from #t1 as t
         join sys.parameters as sp on sp.user_type_id = t.relative_id
         join sys.assembly_modules as am on sp.object_id = am.object_id  
         join sys.objects as o on sp.object_id = o.object_id
         where @iter_no = t.rank and t.relative_type = @udt
      set @rows = @rows + @@rowcount

      --udt that reference assembly
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, at.user_type_id, @udt, @iter_no + 1
         from #t1 as t
         join sys.assembly_types as at on at.assembly_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @assm
      set @rows = @rows + @@rowcount

      --assembly that reference assembly
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, ar.assembly_id, @assm, @iter_no + 1
         from #t1 as t
         join sys.assembly_references as ar on ar.referenced_assembly_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @assm
      set @rows = @rows + @@rowcount

      --table references table
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, fk.parent_object_id, @u, @iter_no + 1
         from #t1 as t
         join sys.foreign_keys as fk on fk.referenced_object_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @u
      set @rows = @rows + @@rowcount

      --table,view references partition scheme
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)      
         select t.relative_id, t.relative_type, idx.object_id, case o.type when 'V' then @v else @u end, @iter_no + 1
         from #t1 as t
         join sys.indexes as idx on idx.data_space_id = t.relative_id 
         join sys.objects as o on o.object_id = idx.object_id
         where @iter_no = t.rank and t.relative_type = @part_sch
      set @rows = @rows + @@rowcount

      --partition scheme references partition function
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)      
         select t.relative_id, t.relative_type, ps.data_space_id, @part_sch, @iter_no + 1
         from #t1 as t
         join sys.partition_schemes as ps on ps.function_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @part_func
      set @rows = @rows + @@rowcount

      --non-schema-bound parameter references type
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, p.object_id, 
               case when obj.type in ( 'P', 'PC' ) then @sp else @udf
               end, @iter_no + 1
         from #t1 as t
         join sys.parameters as p on 
             p.user_type_id = t.relative_id and  t.relative_type in (@uddt, @udt)
         join sys.objects as obj on obj.object_id = p.object_id and obj.type in ( 'P',  'PC', 'TF', 'FN', 'IF', 'FS', 'FT')
         and ISNULL(objectproperty(obj.object_id, 'isschemabound'), 0) = 0
         where @iter_no = t.rank
      set @rows = @rows + @@rowcount

      --view, procedure references table, view, procedure
      --procedure references type
      --table(check) references procedure
      --trigger references table, procedure
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, case when 'C' = obj.type then obj.parent_object_id else dp.object_id end, 
               case when obj.type  in ('U', 'C') then @u when 'V' = obj.type then @v when 'TR' = obj.type then @tr 
               when obj.type in ( 'P', 'RF', 'PC' ) then @sp 
               when obj.type in ( 'TF', 'FN', 'IF', 'FS', 'FT' ) then @udf
               end, @iter_no + 1
         from #t1 as t
         join sys.sql_dependencies as dp on 
            -- reference table, view procedure
            ( class < 2 and dp.referenced_major_id = t.relative_id and t.relative_type in ( @u, @v, @sp, @udf) )
            --reference type
             or ( 2 = class  and dp.referenced_major_id = t.relative_id and  t.relative_type in (@uddt, @udt))
            --reference xml namespace ( not supported by server right now )
            --or ( 3 = class  and dp.referenced_major_id = t.relative_id and @xml = t.relative_type )
         join sys.objects as obj on obj.object_id = dp.object_id and obj.type in ( 'U', 'V', 'P', 'RF', 'PC', 'TR', 'TF', 'FN', 'IF', 'FS', 'FT', 'C')
         where @iter_no = t.rank
      set @rows = @rows + @@rowcount

   end -- 1 = @find_referencing_objects
   else
   begin -- find referenced objects
      --check references table
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, dp.object_id, 77 /*place holder for check*/, @iter_no
         from #t1 as t
         join sys.sql_dependencies as dp on 
            -- reference table
            class < 2 and dp.referenced_major_id = t.relative_id and t.relative_type = @u
         join sys.objects as obj on obj.object_id = dp.object_id and obj.type  = 'C'
         where @iter_no = t.rank
      set @rowcount_ck = @@rowcount

      --non-schema-bound parameter references type
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select distinct 
            t.relative_id,  -- object_id
            t.relative_type,    -- object_type
            p.user_type_id, -- relative_id
            case p.system_type_id when 240 then @udt else @uddt end,
            @iter_no + 1
         from #t1 as t
         join sys.parameters as p on
             p.object_id = t.relative_id and p.user_type_id > 256 and t.relative_type in ( @sp, @udf )
             and ISNULL(objectproperty(p.object_id, 'isschemabound'), 0) = 0
         where @iter_no = t.rank
      set @rows = @rows + @@rowcount

      --view, procedure referenced by table, view, procedure
      --type referenced by procedure
      --check referenced by table
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select distinct 
            case when 77 = t.relative_type then obj2.parent_object_id else t.relative_id end,   -- object_id
            case when 77 = t.relative_type then @u else relative_type end,                -- object_type
            dp.referenced_major_id,                                              -- relative_id
            case                                                           -- relative_type
               when dp.class < 2 then 
                  case when 'U' = obj.type then @u 
                  when 'V' = obj.type then @v 
                  when 'TR' = obj.type then @tr 
                  when 'AF' = obj.type then @uda 
                  when obj.type in ( 'P', 'RF', 'PC' ) then @sp 
                  when obj.type in ( 'TF', 'FN', 'IF', 'FS', 'FT' ) then @udf
                  when exists (select * from sys.synonyms syn where syn.object_id = dp.referenced_major_id ) then @synonym
                  end
               when dp.class = 2 then (case 
                                    when exists (select * from sys.assembly_types sat where sat.user_type_id = dp.referenced_major_id) then @udt 
                                    else @uddt 
                                 end) 
            end, 
            @iter_no + 1
         from #t1 as t
         join sys.sql_dependencies as dp on 
            -- reference table, view procedure
            ( class < 2 and dp.object_id = t.relative_id and t.relative_type in ( @u, @v, @sp, @udf, @tr, @uda, 77) )
            --reference type
             or ( 2 = class  and dp.object_id = t.relative_id ) -- t.relative_type?
            --reference xml namespace ( not supported by server right now )
            --or ( 3 = class  and dp.referenced_major_id = t.relative_id and @xml = t.relative_type )
         left join sys.objects as obj on obj.object_id = dp.referenced_major_id and dp.class < 2 and obj.type in ( 'U', 'V', 'P', 'RF', 'PC', 'TF', 'FN', 'IF', 'FS', 'FT', 'TR', 'AF')
         left join sys.objects as obj2 on obj2.object_id = t.relative_id and 77 = t.relative_type
         where @iter_no = t.rank
      set @rows = @rows + @@rowcount

      if @rowcount_ck > 0 
      begin
         delete from #t1 where relative_type = 77
      end

      --table or view referenced by trigger
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, tr.parent_id, case o.type when 'V' then @v else @u end, @iter_no + 1
         from #t1 as t
         join sys.triggers as tr on tr.object_id = t.relative_id
         join sys.objects as o on o.object_id = tr.parent_id
         where @iter_no = t.rank and t.relative_type = @tr
      set @rows = @rows + @@rowcount

      --table referenced by table
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, fk.referenced_object_id, @u, @iter_no + 1
         from #t1 as t
         join sys.foreign_keys as fk on fk.parent_object_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @u
      set @rows = @rows + @@rowcount

      --assembly referenced by assembly
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, ar.referenced_assembly_id, @assm, @iter_no + 1
         from #t1 as t
         join sys.assembly_references as ar on ar.assembly_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @assm
      set @rows = @rows + @@rowcount

      --assembly referenced by udt
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, at.assembly_id, @assm, @iter_no + 1
         from #t1 as t
         join sys.assembly_types as at on at.user_type_id = t.relative_id
         where @iter_no = t.rank and t.relative_type = @udt
      set @rows = @rows + @@rowcount

      -- assembly referenced by udf, sp, uda, trigger
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, am.assembly_id, @assm, @iter_no + 1
         from #t1 as t
         join sys.assembly_modules as am on am.object_id = t.relative_id
         where @iter_no = t.rank and t.relative_type in ( @udf, @sp, @uda, @tr)
      set @rows = @rows + @@rowcount

      -- udt referenced by CLR udf, sp, uda
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select distinct 
               t.relative_id, 
               t.relative_type, 
               at.user_type_id, 
               @udt, 
               @iter_no + 1
         from #t1 as t
         join sys.parameters as sp on sp.object_id = t.relative_id
         join sys.assembly_modules as am on am.object_id = sp.object_id
         join sys.assembly_types as at on sp.user_type_id = at.user_type_id
         where @iter_no = t.rank and t.relative_type in (@udf, @sp, @uda)
      set @rows = @rows + @@rowcount

      --clr types referenced by tables ( types referenced by parameters are in sql_dependencies )
      insert #t1 (object_id, object_type, relative_id, relative_type, rank)
         select t.relative_id, t.relative_type, c.user_type_id, @udt, @iter_no + 1
         from #t1 as t
         join sys.columns as c on  c.object_id = t.relative_id
         join sys.assembly_types as tp on tp.user_type_id = c.user_type_id
         where @iter_no = t.rank and t.relative_type = @u
      set @rows = @rows + @@rowcount

   end
   set @iter_no = @iter_no + 1
end --main loop

--objects that don't need to be in the loop because they don't reference anybody
if( 0 = @find_referencing_objects )
begin
   --alias types referenced by tables ( types referenced by parameters are in sql_dependencies )
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)
      select t.relative_id, t.relative_type, c.user_type_id, @uddt, @iter_no + 1
      from #t1 as t
      join sys.columns as c on  c.object_id = t.relative_id
      join sys.types as tp on tp.user_type_id = c.user_type_id and tp.is_user_defined = 1
      where t.relative_type = @u and tp.is_assembly_type = 0

   if @@rowcount > 0 
   begin
      set @iter_no = @iter_no + 1
   end

   --defaults referenced by types
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)
      select t.relative_id, t.relative_type, tp.default_object_id, @def, @iter_no + 1
      from #t1 as t
      join sys.types as tp on tp.user_type_id = t.relative_id and tp.default_object_id > 0
      join sys.objects as o on o.object_id = tp.default_object_id and 0 = isnull(o.parent_object_id, 0)
      where t.relative_type = @uddt

   --defaults referenced by tables( only default objects )
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)
      select t.relative_id, t.relative_type, clmns.default_object_id, @def, @iter_no + 1
      from #t1 as t
      join sys.columns as clmns on clmns.object_id = t.relative_id
      join sys.objects as o on o.object_id = clmns.default_object_id and 0 = isnull(o.parent_object_id, 0)
      where t.relative_type = @u

   --rules referenced by types
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)
      select t.relative_id, t.relative_type, tp.rule_object_id, @rule, @iter_no + 1
      from #t1 as t
      join sys.types as tp on tp.user_type_id = t.relative_id and tp.rule_object_id > 0
      where t.relative_type = @uddt

   --rules referenced by tables
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)
      select t.relative_id, t.relative_type, clmns.rule_object_id, @rule, @iter_no + 1
      from #t1 as t
      join sys.columns as clmns on clmns.object_id = t.relative_id and clmns.rule_object_id > 0
      where t.relative_type = @u

   --XmlSchemaCollections referenced by table
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)
      select t.relative_id, t.relative_type, c.xml_collection_id, @xml, @iter_no + 1
      from #t1 as t
      join sys.columns as c on c.object_id = t.relative_id and c.xml_collection_id > 0
      where t.relative_type = @u

   --XmlSchemaCollections referenced by procedures
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)
      select t.relative_id, t.relative_type, c.xml_collection_id, @xml, @iter_no + 1
      from #t1 as t
      join sys.parameters as c on c.object_id = t.relative_id and c.xml_collection_id > 0
      where t.relative_type in ( @sp, @udf)

   --partition scheme referenced by table,view
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)      
      select t.relative_id, t.relative_type, ps.data_space_id, @part_sch, @iter_no + 1
      from #t1 as t
      join sys.indexes as idx on idx.object_id = t.relative_id 
      join sys.partition_schemes as ps on ps.data_space_id = idx.data_space_id
      where t.relative_type in (@u, @v)

   --partition function referenced by partition scheme
   insert #t1 (object_id, object_type, relative_id, relative_type, rank)      
      select t.relative_id, t.relative_type, ps.function_id, @part_func, @iter_no + 1
      from #t1 as t
      join sys.partition_schemes as ps on ps.data_space_id = t.relative_id
      where t.relative_type = @part_sch

end

--cleanup circular references
delete #t1 where object_id = relative_id and object_type=relative_type

--allow circular dependencies by cuting one of the branches
--mark as soft links dependencies between tables
-- at script time we will need to take care to script fks and checks separately
update #t1 set soft_link = 1 where ( object_type = @u and relative_type = @u )

--add independent objects first in the list
insert #t1 ( object_id, object_type, rank) 
   select t.relative_id, t.relative_type, 1 from #t1 t where t.relative_id not in ( select t2.object_id from #t1 t2 where not t2.object_id is null )

--delete initial objects
delete #t1 where object_id is null

-- compute the surrogate keys to make sorting easier
update #t1 set object_key = object_id + convert(bigint, 0xfFFFFFFF) * object_type
update #t1 set relative_key = relative_id + convert(bigint, 0xfFFFFFFF) * relative_type

create index index_key on #t1 (object_key, relative_key)

update #t1 set rank = 0
-- computing the degree of the nodes
update #t1 set degree = (
      select count(*) 
      from #t1 t_alias 
      where t_alias.object_key = #t1.object_key and 
            t_alias.relative_id is not null and 
            t_alias.soft_link is null)

-- perform topological sorting 
set @iter_no=1
while 1=1
begin 
   update #t1 set rank=@iter_no where degree=0
   -- end the loop if no more rows left to process
   if (@@rowcount=0) break
   update #t1 set degree=NULL where rank = @iter_no

   update #t1 set degree = (
      select count(*) 
         from #t1 t_alias 
         where t_alias.object_key = #t1.object_key and 
            t_alias.relative_key is not null and 
            t_alias.relative_key in (select t_alias2.object_key from #t1 t_alias2 where t_alias2.rank=0 and t_alias2.soft_link is null) and
            t_alias.rank=0 and t_alias.soft_link is null)
      where degree is not null

   set @iter_no=@iter_no+1
end

--add name schema
update #t1 set object_name = o.name, object_schema = schema_name(o.schema_id)
from sys.objects AS o 
where o.object_id = #t1.object_id and object_type in ( @u, @udf, @v, @sp, @def, @rule, @uda)

update #t1 set relative_type = case op.type when 'V' then @v else @u end, object_name = o.name, object_schema = schema_name(o.schema_id), relative_name = op.name, relative_schema = schema_name(op.schema_id)
from sys.objects AS o 
join sys.objects AS op on op.object_id = o.parent_object_id
where o.object_id = #t1.object_id and object_type = @tr

update #t1 set object_name = t.name, object_schema = schema_name(t.schema_id)
from sys.types AS t
where t.user_type_id = #t1.object_id and object_type in ( @uddt, @udt )

update #t1 set object_name = x.name, object_schema = schema_name(x.schema_id)
from sys.xml_schema_collections AS x
where x.xml_collection_id = #t1.object_id and object_type = @xml

update #t1 set object_name = p.name, object_schema = null
from sys.partition_schemes AS p
where p.data_space_id = #t1.object_id and object_type = @part_sch


update #t1 set object_name = p.name, object_schema = null
from sys.partition_functions AS p
where p.function_id = #t1.object_id and object_type = @part_func

update #t1 set object_name = a.name, object_schema = null
from sys.assemblies AS a
where a.assembly_id = #t1.object_id and object_type = @assm

update #t1 set object_name = syn.name, object_schema = schema_name(syn.schema_id)
from sys.synonyms AS syn
where syn.object_id = #t1.object_id and object_type = @synonym

-- delete objects for which we could not resolve the table name or schema
-- because we may not have enough privileges
delete from #t1 
where 
    object_name is null or 
    (object_schema is null  and object_type not in (@assm, @part_func, @part_sch))

--final select
select object_id, object_type, relative_id, relative_type, object_name, object_schema, relative_name, relative_schema
 from #t1 
 order by rank, relative_id

drop table #t1
drop table #tempdep

IF @must_set_nocount_off > 0 
   set nocount off