Reg. Foreign Key Reference Tree..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Reg. Foreign Key Reference Tree..

Author

Topic

ourspt
Starting Member

33 Posts

Posted - 2005-01-12 : 03:22:20

Hi,

In one of my projects I have a requirement where I get all the dependent tables for a particular Table.

For Example, say Table A has foriegn key relations to B, C,D
Say Table B has foriegn key relation to E,F,G
Say Table C has Foreign key relation to H,I,G
Table E has foreign Key relation to U, V, W
Table U has foreign key relation to X, Y, Z

Then, is there any way, where if I give Table A, I will get the list of all the tables referencing it (directly or indirectly). The result should retrun all the tables - B, C, D, E, F, G, H, I, G, U, V, W, X, Y, Z

Any system function or piece of code would be great.

Thanks
oursmp

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-12 : 05:39:07

use Northwind
GO
if object_id('tempdb..#t')>0 drop table #t
create table #t(i int identity(1,1), tn sysname)

declare @i int, @tn sysname
select @i=1, @tn='Customers'
insert into #t select @tn

while 1=1
begin
select @i=@i+1, @tn=tn from #t where i=@i
if @@rowcount=0 break
insert into #t exec my_fkeys @tn
end

select * from #t


i           tn                         
----------- ----------------------- 
1           Customers
2           CustomerCustomerDemo
3           Orders
4           Order Details

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-12 : 05:53:35

What is my_fkeys.
I just copied the code text of master..sp_fkeys into QA
and in the very end of it I replaced its final "select" with


	/* select count (*) as 'XXX countout' from #fkeysout */

		select FKTABLE_NAME
		from #fkeysout
		order by 1

... renamed it, run it (while in Northwind).

ourspt
Starting Member

33 Posts

Posted - 2005-01-12 : 06:26:06

Dear Stoad,

Thanks for the quick response. When I executed the script, giving my database name and a sample table, it is giving me an error, "Could not find stored procedure 'my_fkeys'."

Please respond.

Thanks
ourspt

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-12 : 06:41:06

Below its text (of my_fkeys stored procedure):


SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  OFF 
GO



/*	Procedure for 7.0 and later servers */
CREATE PROCEDURE my_fkeys(
			   @pktable_name		sysname = null,
			   @pktable_owner		sysname = null,
			   @pktable_qualifier	sysname = null,
			   @fktable_name		sysname = null,
			   @fktable_owner		sysname = null,
			   @fktable_qualifier	sysname = null )
as
	set nocount on
	DECLARE @pktable_id			int
	DECLARE @pkfull_table_name	nvarchar(257) /* 2*128 + 1 */
	DECLARE @fktable_id			int
	DECLARE @fkfull_table_name	nvarchar(257) /* 2*128 + 1 */
	declare	@order_by_pk		int

	/* select 'XXX starting table creation' */

	create table #fkeysall(
			rkeyid int NOT NULL,
			rkey1 int NOT NULL,
				rkey2 int NOT NULL,
				rkey3 int NOT NULL,
				rkey4 int NOT NULL,
				rkey5 int NOT NULL,
				rkey6 int NOT NULL,
				rkey7 int NOT NULL,
				rkey8 int NOT NULL,
				rkey9 int NOT NULL,
				rkey10 int NOT NULL,
				rkey11 int NOT NULL,
				rkey12 int NOT NULL,
				rkey13 int NOT NULL,
				rkey14 int NOT NULL,
				rkey15 int NOT NULL,
				rkey16 int NOT NULL,
			fkeyid int NOT NULL,
			fkey1 int NOT NULL,
				fkey2 int NOT NULL,
				fkey3 int NOT NULL,
				fkey4 int NOT NULL,
				fkey5 int NOT NULL,
				fkey6 int NOT NULL,
				fkey7 int NOT NULL,
				fkey8 int NOT NULL,
				fkey9 int NOT NULL,
				fkey10 int NOT NULL,
				fkey11 int NOT NULL,
				fkey12 int NOT NULL,
				fkey13 int NOT NULL,
				fkey14 int NOT NULL,
				fkey15 int NOT NULL,
				fkey16 int NOT NULL,
			constid int NOT NULL,
			name sysname NOT NULL)

	create table #fkeys(
			pktable_id		int NOT NULL,
			pkcolid 		int NOT NULL,
			fktable_id		int NOT NULL,
			fkcolid 		int NOT NULL,
			KEY_SEQ 		smallint NOT NULL,
			fk_id			int NOT NULL,
			PK_NAME			sysname NOT NULL)

	create table #fkeysout(
			PKTABLE_QUALIFIER sysname NULL,
			PKTABLE_OWNER sysname NULL,
			PKTABLE_NAME sysname NOT NULL,
			PKCOLUMN_NAME sysname NOT NULL,
			FKTABLE_QUALIFIER sysname NULL,
			FKTABLE_OWNER sysname NULL,
			FKTABLE_NAME sysname NOT NULL,
			FKCOLUMN_NAME sysname NOT NULL,
			KEY_SEQ smallint NOT NULL,
			UPDATE_RULE smallint NULL,
			DELETE_RULE smallint NULL,
			FK_NAME sysname NULL,
			PK_NAME sysname NULL,
			DEFERRABILITY smallint null)

	/* select 'XXX starting parameter analysis' */

    select  @order_by_pk = 0

	if (@pktable_name is null) and (@fktable_name is null)
	begin	/* If neither primary key nor foreign key table names given */
		raiserror (15252,-1,-1)
		return
    end
	if @fktable_qualifier is not null
    begin
		if db_name() <> @fktable_qualifier
		begin	/* If qualifier doesn't match current database */
			raiserror (15250, -1,-1)
			return
		end
    end
	if @pktable_qualifier is not null
    begin
		if db_name() <> @pktable_qualifier
		begin	/* If qualifier doesn't match current database */
			raiserror (15250, -1,-1)
			return
		end
    end

	if @pktable_owner is null
	begin	/* If unqualified primary key table name */
		SELECT @pkfull_table_name = quotename(@pktable_name)
    end
    else
	begin	/* Qualified primary key table name */
		if @pktable_owner = ''
		begin	/* If empty owner name */
			SELECT @pkfull_table_name = quotename(@pktable_owner)
		end
		else
		begin
			SELECT @pkfull_table_name = quotename(@pktable_owner) +
				'.' + quotename(@pktable_name)
		end
    end
	/*	Get Object ID */
	SELECT @pktable_id = object_id(@pkfull_table_name)

	if @fktable_owner is null
	begin	/* If unqualified foreign key table name */
		SELECT @fkfull_table_name = quotename(@fktable_name)
    end
    else
	begin	/* Qualified foreign key table name */
		if @fktable_owner = ''
		begin	/* If empty owner name */
			SELECT @fkfull_table_name = quotename(@fktable_owner)
		end
		else
		begin
			SELECT @fkfull_table_name = quotename(@fktable_owner) +
				'.' + quotename(@fktable_name)
		end
    end
	/*	Get Object ID */
	SELECT @fktable_id = object_id(@fkfull_table_name)

	if @fktable_name is not null

	begin
		if @fktable_id is null
			SELECT @fktable_id = 0	/* fk table not found, empty result */
    end

	if @pktable_name is null
	begin /*  If table name not supplied, match all */
		select @order_by_pk = 1
	end
	else
	begin
		if @pktable_id is null
		begin
			SELECT @pktable_id = 0	/* pk table not found, empty result */
		end
	end

	/*	SQL Server supports upto 16 PK/FK relationships between 2 tables */
	/*	Process syskeys for each relationship */
	/*  First, attempt to get all 16 keys for each rel'ship, then sort
		them out with a 16-way "insert select ... union select ..." */

	/* select 'XXX starting data analysis' */

	insert into #fkeysall
		select
			r.rkeyid,
			r.rkey1, r.rkey2, r.rkey3, r.rkey4,
				r.rkey5, r.rkey6, r.rkey7, r.rkey8,
				r.rkey9, r.rkey10, r.rkey11, r.rkey12,
				r.rkey13, r.rkey14, r.rkey15, r.rkey16,
			r.fkeyid,
			r.fkey1, r.fkey2, r.fkey3, r.fkey4,
				r.fkey5, r.fkey6, r.fkey7, r.fkey8,
				r.fkey9, r.fkey10, r.fkey11, r.fkey12,
				r.fkey13, r.fkey14, r.fkey15, r.fkey16,
			r.constid,
			i.name
		from
			sysreferences r, sysobjects o, sysindexes i
		where	r.constid = o.id
			AND o.xtype = 'F'
			AND r.rkeyindid = i.indid
			AND r.rkeyid = i.id
			AND r.rkeyid between isnull(@pktable_id, 0)
							and isnull(@pktable_id, 0x7fffffff)
			AND r.fkeyid between isnull(@fktable_id, 0)
							and isnull(@fktable_id, 0x7fffffff)

	/* select count (*) as 'XXX countall' from #fkeysall */

    insert into #fkeys
			select rkeyid, rkey1, fkeyid, fkey1, 1, constid, name
			from #fkeysall
		union all
			select rkeyid, rkey2, fkeyid, fkey2, 2, constid, name
			from #fkeysall
		union all
			select rkeyid, rkey3, fkeyid, fkey3, 3, constid, name
			from #fkeysall
		union all
			select rkeyid, rkey4, fkeyid, fkey4, 4, constid, name
			from #fkeysall
		union all
			select rkeyid, rkey5, fkeyid, fkey5, 5, constid, name
			from #fkeysall
		union all
			select rkeyid, rkey6, fkeyid, fkey6, 6, constid, name
			from #fkeysall
		union all
			select rkeyid, rkey7, fkeyid, fkey7, 7, constid, name
			from #fkeysall
		union all
			select rkeyid, rkey8, fkeyid, fkey8, 8, constid, name
			from #fkeysall
		union all
			select rkeyid, rkey9, fkeyid, fkey9, 9, constid, name
			from #fkeysall
		union all
			select rkeyid, rkey10, fkeyid, fkey10, 10, constid, name
			from #fkeysall
		union all
			select rkeyid, rkey11, fkeyid, fkey11, 11, constid, name
			from #fkeysall
		union all
			select rkeyid, rkey12, fkeyid, fkey12, 12, constid, name
			from #fkeysall
		union all
			select rkeyid, rkey13, fkeyid, fkey13, 13, constid, name
			from #fkeysall
		union all
			select rkeyid, rkey14, fkeyid, fkey14, 14, constid, name
			from #fkeysall
		union all
			select rkeyid, rkey15, fkeyid, fkey15, 15, constid, name
			from #fkeysall
		union all
			select rkeyid, rkey16, fkeyid, fkey16, 16, constid, name
			from #fkeysall

	/* select count (*) as 'XXX count' from #fkeys */

	insert into #fkeysout
		select
			PKTABLE_QUALIFIER = convert(sysname,db_name()),
			PKTABLE_OWNER = convert(sysname,USER_NAME(o1.uid)),
			PKTABLE_NAME = convert(sysname,o1.name),
			PKCOLUMN_NAME = convert(sysname,c1.name),
			FKTABLE_QUALIFIER = convert(sysname,db_name()),
			FKTABLE_OWNER = convert(sysname,USER_NAME(o2.uid)),
			FKTABLE_NAME = convert(sysname,o2.name),
			FKCOLUMN_NAME = convert(sysname,c2.name),
			KEY_SEQ,
			UPDATE_RULE = convert(smallint,1),
			DELETE_RULE = convert(smallint,1),
			FK_NAME = convert(sysname,OBJECT_NAME(fk_id)),
			PK_NAME,
			DEFERRABILITY = 7	/* SQL_NOT_DEFERRABLE */
		from #fkeys f,
			sysobjects o1, sysobjects o2,
			syscolumns c1, syscolumns c2
		where	o1.id = f.pktable_id
			AND o2.id = f.fktable_id
			AND c1.id = f.pktable_id
			AND c2.id = f.fktable_id
			AND c1.colid = f.pkcolid
			AND c2.colid = f.fkcolid

	/* select count (*) as 'XXX countout' from #fkeysout */

		select FKTABLE_NAME
		from #fkeysout
		order by 1

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources