SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Making Good Use of Sysforeignkeys Table - Part 1: Display table relati


Making Good Use of Sysforeignkeys Table - Part 1: Display table relati

Author
Message
jeffrey yao
jeffrey yao
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1081 Visits: 896
Comments posted to this topic are about the content posted at http://www.sqlservercentral



robertm
robertm
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 Visits: 118

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





Sameer Raval
Sameer Raval
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1050 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
jeffrey yao
jeffrey yao
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1081 Visits: 896

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.





Kenneth Lee
Kenneth Lee
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 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.
Kenneth Lee
Kenneth Lee
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 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


Kenneth Lee
Kenneth Lee
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 1
Sorry about the double spacing. That's this posting routine getting involved.
Kenneth Lee
Kenneth Lee
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 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


Salvador Anthony Desa
Salvador Anthony Desa
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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.


jeffrey yao
jeffrey yao
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1081 Visits: 896

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

Jeffrey





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search