Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Making Good Use of Sysforeignkeys Table - Part 1: Display table relati Expand / Collapse
Author
Message
Posted Wednesday, May 26, 2004 9:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:53 AM
Points: 226, Visits: 688
Comments posted to this topic are about the content posted at http://www.sqlservercentral


Post #117824
Posted Wednesday, June 01, 2005 3:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 3:04 AM
Points: 210, Visits: 115

This is a really great use of data in the sys tables of your database, especially when it comes to providing documentation.

One small bug in the procedure however is that it will duplicate parent child relationships in the resultant tree between two tables where the relationship is composed of a composite key. To rectify this you need to simply pouplate the cursor curChild with a distinct list of foreign keys, e.g.

declare curChild cursor local for
        select distinct object_name(fkeyid) as child from sysforeignkeys
        where rkeyid = object_id(@table_name) and rkeyid <> fkeyid




Post #186487
Posted Wednesday, June 01, 2005 7:23 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 13, 2013 8:04 PM
Points: 545, Visits: 255

This looks to be very good SP.

I have small problem creating this sp , get following message

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'usp_DisplayTableRelation'. The stored procedure will still be created.

Still, I continued but not resulting anything else then tblname that is input.

Will appriciate your help to resolve the problem..

 

Thanks,

Sameer

 

 

 



Kindest Regards,

Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com

Post #186535
Posted Wednesday, June 01, 2005 2:06 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:53 AM
Points: 226, Visits: 688

Thanks, Robertm, for pointing out the bug. Really appreciate it !

Sameer, I have replied your email. Pls check it.

This SP will only display the relationship tree from the top to bottom. So if your table is at on the leaf level of a tree, the SP will only display the table itself.

In future, I may revise this SP by adding a "switch" control to allow display a "reverse" relationship tree, i.e. from the bottom to top.  

I am honored if my script can be of any help to SCC community.




Post #186781
Posted Wednesday, June 01, 2005 2:57 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 18, 2005 4:21 PM
Points: 75, Visits: 1
 I immediately blew up on a recursive loop count error, basically because your code can put the table references in an infinite loop.  Here's an extract of the output after I modified your code.
   |---Contact
        |---Contact
        |---MortgageInsurance
            |---ContactOffice
                |---LoanFeeTemplate
                    |---Contact (Loop Reference)
First I put in a temp table to track what tables have already had tree output.  Oh, yuck, now there's all these add row messages in the middle of your print.  OK add a second table to hold the output.  Hmmm, now there's maintenance of these temp tables and no way for your recursive loop to know when the table is done.  Make two procs, one to handle the tables and one to recursively loop through the list.  Hmmm, your self-refference is on the same level, may as well fix that as well.  I'll put my finished code (that also takes care of the error message your newbie had.) in a new post.
Post #186790
Posted Wednesday, June 01, 2005 2:58 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 18, 2005 4:21 PM
Points: 75, Visits: 1

if NOT exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_DisplayTableRelation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

exec sp_executesql N'CREATE procedure usp_DisplayTableRelation AS print 1'

if NOT exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_DisplayTableRelation1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

exec sp_executesql N'CREATE procedure usp_DisplayTableRelation1 AS print 1'

go

alter procedure usp_DisplayTableRelation

@table_name nvarchar(128) -- the procedure will try to find all the child tables for the table @table_name

as

begin -- sp

if @table_name is null

return

if not exists (select * from tempdb.dbo.sysobjects where name = N'##UsedTableName' )

create table ##UsedTableName (TableName nvarchar(128))-- use to track recursive calls to same table

else delete from ##UsedTableName

if not exists (select * from tempdb.dbo.sysobjects where name = N'##DisplayTableRelation' )

create table ##DisplayTableRelation (Relation nvarchar(4000))-- use to track recursive calls to same table

else delete from ##DisplayTableRelation

exec usp_DisplayTableRelation1 @table_name = @table_name

select * from ##DisplayTableRelation

drop table ##UsedTableName

drop table ##DisplayTableRelation

return

end --sp

go

alter procedure usp_DisplayTableRelation1

@table_name nvarchar(128) -- the procedure will try to find all the child tables for the table @table_name

, @space_len int = -4 output -- for insert into ##DisplayTableRelation (Relation) selecting position purpose

as

begin -- sp

declare @child nvarchar(128)

declare @usedTable nvarchar(100)

if @table_name is null

return

if not exists (select * from tempdb.dbo.sysobjects where name = N'##DisplayTableRelation' )

begin

exec usp_DisplayTableRelation @table_name

return

end

set @space_len = @space_len + 4

if not exists (select * from sysforeignkeys where rkeyid = object_id(@table_name) )

begin -- leaf level

if @space_len <= 0

insert into ##DisplayTableRelation (Relation) select @table_name

else

insert into ##DisplayTableRelation (Relation) select space(@space_len) + '|---' + @table_name

set @space_len = @space_len - 4

return

end -- leaf level

else -- the @table_name table exists

begin -- else

set @usedTable = N''

if exists (select * from ##UsedTableName where TableName = @table_name)

set @usedTable = N' (Loop Reference)'

else insert into ##UsedTableName (TableName) select @table_name

if @space_len <= 0

insert into ##DisplayTableRelation (Relation) select @table_name + @usedTable

else

insert into ##DisplayTableRelation (Relation) select space(@space_len) + '|---' + @table_name + @usedTable

if (@usedTable = N'' --first time

and exists ( select * from sysforeignkeys where rkeyid = object_id(@table_name) and rkeyid = fkeyid)) -- self referenced

insert into ##DisplayTableRelation (Relation) select space(@space_len+4) + '|---' + @table_name

declare curChild cursor local for

select object_name(fkeyid) as child from sysforeignkeys

where rkeyid = object_id(@table_name) and rkeyid <> fkeyid

open curChild

fetch next from curChild into @child

while @@fetch_status = 0

begin -- cursor loop

if (@usedTable = N'') exec usp_DisplayTableRelation1 @table_name = @child, @space_len = @space_len output

fetch next from curChild into @child

end -- cursor loop

close curChild

deallocate curChild

end --else

set @space_len = @space_len - 4

return

end --sp

Post #186791
Posted Wednesday, June 01, 2005 2:59 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 18, 2005 4:21 PM
Points: 75, Visits: 1
Sorry about the double spacing.  That's this posting routine getting involved.
Post #186793
Posted Wednesday, June 01, 2005 3:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 18, 2005 4:21 PM
Points: 75, Visits: 1

if NOT exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_DisplayTableRelation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 exec sp_executesql N'CREATE procedure usp_DisplayTableRelation AS print 1'
if NOT exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_DisplayTableRelation1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 exec sp_executesql N'CREATE procedure usp_DisplayTableRelation1 AS print 1'
go

alter procedure usp_DisplayTableRelation
  @table_name nvarchar(128) -- the procedure will try to find all the child tables for the table @table_name
as
begin -- sp
    if @table_name is null
        return
 if not exists (select * from tempdb.dbo.sysobjects where name = N'##UsedTableName' )
  create table ##UsedTableName (TableName nvarchar(128))-- use to track recursive calls to same table
 else delete from ##UsedTableName
 if not exists (select * from tempdb.dbo.sysobjects where name = N'##DisplayTableRelation' )
  create table ##DisplayTableRelation (Relation nvarchar(4000))-- use to track recursive calls to same table
 else delete from ##DisplayTableRelation

    exec usp_DisplayTableRelation1 @table_name = @table_name

    select * from ##DisplayTableRelation
   
    drop table ##UsedTableName
    drop table ##DisplayTableRelation
    return
end --sp
go

alter procedure usp_DisplayTableRelation1
  @table_name nvarchar(128) -- the procedure will try to find all the child tables for the table @table_name
, @space_len int = -4 output -- for insert into ##DisplayTableRelation (Relation) selecting position purpose
as
begin -- sp
    declare @child nvarchar(128)
    declare @usedTable nvarchar(100)
    if @table_name is null
        return
 if not exists (select * from tempdb.dbo.sysobjects where name = N'##DisplayTableRelation' )
 begin
  exec usp_DisplayTableRelation @table_name
  return
 end
    set @space_len = @space_len + 4

    if not exists (select * from sysforeignkeys where rkeyid = object_id(@table_name) )
    begin -- leaf level
        if @space_len <= 0
            insert into ##DisplayTableRelation (Relation) select @table_name
        else
            insert into ##DisplayTableRelation (Relation) select space(@space_len) + '|---' + @table_name

        set @space_len = @space_len - 4
        return
    end -- leaf level
    else -- the @table_name table exists
    begin -- else
  set @usedTable = N''
  if exists (select * from ##UsedTableName where TableName = @table_name)
   set @usedTable = N' (Loop Reference)'
  else insert into ##UsedTableName (TableName) select @table_name
        if @space_len <= 0
            insert into ##DisplayTableRelation (Relation) select @table_name + @usedTable
        else
            insert into ##DisplayTableRelation (Relation) select space(@space_len) + '|---' + @table_name + @usedTable

        if (@usedTable = N'' --first time
   and exists ( select * from sysforeignkeys where rkeyid = object_id(@table_name) and rkeyid = fkeyid)) -- self referenced
    insert into ##DisplayTableRelation (Relation) select space(@space_len+4) + '|---' + @table_name

        declare curChild cursor local for
        select object_name(fkeyid) as child from sysforeignkeys
        where rkeyid = object_id(@table_name) and rkeyid <> fkeyid

        open curChild
        fetch next from curChild into @child
       
        while @@fetch_status = 0
         begin -- cursor loop
             if (@usedTable = N'') exec usp_DisplayTableRelation1 @table_name = @child, @space_len = @space_len output
             fetch next from curChild into @child
          end -- cursor loop

           close curChild
           deallocate curChild
    end --else
    set @space_len = @space_len - 4
    return
end --sp
-- copied to notepad, then copied it to this post

Post #186794
Posted Friday, October 21, 2005 7:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 18, 2012 7:17 PM
Points: 1, Visits: 5

Thank you so much for such a wonderfull piece of code. I was working on a project and what you have in your code is exactly what I was looking for...

Its really a very good piece of code to keep you document upto date with minmum efforts.

Post #231146
Posted Thursday, January 05, 2006 10:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:53 AM
Points: 226, Visits: 688

Thanks, Kenneth for your great work to help improve my code. Really appreciate it !

Jeffrey




Post #248552
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse