SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Find Table Reference Levels
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/03/2006 :  22:21:59  Show Profile  Reply with Quote
The script below can be used to determine the reference levels of all tables in a database in order to be able to create a script to load tables in the correct order to prevent Foreign Key violations.

This script returns 3 result sets. The first shows the tables in order by level and table name. The second shows tables and tables that reference it in order by table and referencing table. The third shows tables and tables it references in order by table and referenced table.

Tables at level 0 have no related tables, except self-references. Tables at level 1 reference no other table, but are referenced by other tables. Tables at levels 2 and above are tables which reference lower level tables and may be referenced by higher levels. Tables with a level of NULL may indicate a circular reference (example: TableA references TableB and TableB references TableA).

Tables at levels 0 and 1 can be loaded first without FK violations, and then the tables at higher levels can be loaded in order by level from lower to higher to prevent FK violations. All tables at the same level can be loaded at the same time without FK violations.

Tested on SQL 2000 only. Please post any errors found.

Edit 2006/10/10:
Fixed bug with tables that have multiple references, and moved tables that have only self-references to level 1 from level 0.


-- Start of Script - Find_Table_Reference_Levels.sql
/*
Find Table Reference Levels

This script finds table references and ranks them by level in order
to be able to load tables with FK references in the correct order.
Tables can then be loaded one level at a time from lower to higher.
This script also shows all the relationships for each table
by tables it references and by tables that reference it.

Level 0 is tables which have no FK relationships.

Level 1 is tables which reference no other tables, except
themselves, and are only referenced by higher level tables
or themselves.

Levels 2 and above are tables which reference lower levels
and may be referenced by higher levels or themselves.

*/

declare @r table (
PK_TABLE nvarchar(200),
FK_TABLE nvarchar(200),
primary key clustered (PK_TABLE,FK_TABLE))

declare @rs table (
PK_TABLE nvarchar(200),
FK_TABLE nvarchar(200),
primary key clustered (PK_TABLE,FK_TABLE))

declare @t table (
REF_LEVEL int,
TABLE_NAME nvarchar(200) not null primary key clustered )

declare @table table (
TABLE_NAME nvarchar(200) not null primary key clustered )
set nocount off

print 'Load tables for database '+db_name()

insert into @table
select
	TABLE_NAME = a.TABLE_SCHEMA+'.'+a.TABLE_NAME
from
	INFORMATION_SCHEMA.TABLES a
where
	a.TABLE_TYPE = 'BASE TABLE'	and
	a.TABLE_SCHEMA+'.'+a.TABLE_NAME <> 'dbo.dtproperties'
order by
	1

print 'Load PK/FK references'
insert into @r
select	distinct
	PK_TABLE = 
	b.TABLE_SCHEMA+'.'+b.TABLE_NAME,
	FK_TABLE = 
	c.TABLE_SCHEMA+'.'+c.TABLE_NAME
from
	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
	join
	INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
	on
	a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and
	a.UNIQUE_CONSTRAINT_NAME = b.CONSTRAINT_NAME
	join
	INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
	on
	a.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA and
	a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
order by
	1,2

print 'Make copy of PK/FK references'
insert into @rs
select 
	*
from
	@r
order by
	1,2

print 'Load un-referenced tables as level 0'
insert into @t
select
	REF_LEVEL = 0,
	a.TABLE_NAME
from
	@table a
where
	a.TABLE_NAME not in
	(
	select PK_TABLE from @r union all 
	select FK_TABLE from @r
	)
order by
	1

-- select * from @r
print 'Remove self references'
delete from @r
where
	PK_TABLE = FK_TABLE

declare @level int
set @level = 0

while @level < 100
	begin
	set @level = @level + 1

	print 'Delete lower level references'
	delete from @r
	where
		PK_TABLE in 
		( select TABLE_NAME from @t )
		or
		FK_TABLE in 
		( select TABLE_NAME from @t )

	print 'Load level '+convert(varchar(20),@level)+' tables'

	insert into @t
	select
		REF_LEVEL =@level,
		a.TABLE_NAME
	from
		@table a
	where
		a.TABLE_NAME not in
		( select FK_TABLE from @r )
		and
		a.TABLE_NAME not in
		( select TABLE_NAME from @t )
	order by
		1

	if not exists (select * from  @r )
		begin
		print 'Done loading table levels'
		print ''
		break
		end

	end


print 'Count of Tables by level'
print ''

select
	REF_LEVEL,
	TABLE_COUNT = count(*)
from 
	@t
group by
	REF_LEVEL
order by
	REF_LEVEL

print 'Tables in order by level and table name'
print 'Note: Null REF_LEVEL nay indicate possible circular reference'
print ''
select
	b.REF_LEVEL,
	TABLE_NAME = convert(varchar(40),a.TABLE_NAME)
from 
	@table a
	left join
	@t b
	on a.TABLE_NAME = b.TABLE_NAME
order by
	b.REF_LEVEL,
	a.TABLE_NAME

print 'Tables and Referencing Tables'
print ''
select
	b.REF_LEVEL,
	TABLE_NAME = convert(varchar(40),a.TABLE_NAME),
	REFERENCING_TABLE =convert(varchar(40),c.FK_TABLE)
from 
	@table a
	left join
	@t b
	on a.TABLE_NAME = b.TABLE_NAME
	left join
	@rs c
	on a.TABLE_NAME = c.PK_TABLE
order by
	a.TABLE_NAME,
	c.FK_TABLE


print 'Tables and Tables Referenced'
print ''
select
	b.REF_LEVEL,
	TABLE_NAME = convert(varchar(40),a.TABLE_NAME),
	TABLE_REFERENCED =convert(varchar(40),c.PK_TABLE)
from 
	@table a
	left join
	@t b
	on a.TABLE_NAME = b.TABLE_NAME
	left join
	@rs c
	on a.TABLE_NAME = c.FK_TABLE
order by
	a.TABLE_NAME,
	c.PK_TABLE


-- End of Script



Results from Northwind database:

Load tables for database Northwind

(13 row(s) affected)

Load PK/FK references

(13 row(s) affected)

Make copy of PK/FK references

(13 row(s) affected)

Load un-referenced tables as level 0

(0 row(s) affected)

Remove self references

(1 row(s) affected)

Delete lower level references

(0 row(s) affected)

Load level 1 tables

(7 row(s) affected)

Delete lower level references

(9 row(s) affected)

Load level 2 tables

(4 row(s) affected)

Delete lower level references

(3 row(s) affected)

Load level 3 tables

(2 row(s) affected)

Done loading table levels
 
Count of Tables by level
 
REF_LEVEL   TABLE_COUNT 
----------- ----------- 
1           7
2           4
3           2

(3 row(s) affected)

Tables in order by level and table name
Note: Null REF_LEVEL nay indicate possible circular reference
 
REF_LEVEL   TABLE_NAME                               
----------- ---------------------------------------- 
1           dbo.Categories
1           dbo.CustomerDemographics
1           dbo.Customers
1           dbo.Employees
1           dbo.Region
1           dbo.Shippers
1           dbo.Suppliers
2           dbo.CustomerCustomerDemo
2           dbo.Orders
2           dbo.Products
2           dbo.Territories
3           dbo.EmployeeTerritories
3           dbo.Order Details

(13 row(s) affected)

Tables and Referencing Tables
 
REF_LEVEL   TABLE_NAME                               REFERENCING_TABLE                        
----------- ---------------------------------------- ---------------------------------------- 
1           dbo.Categories                           dbo.Products
2           dbo.CustomerCustomerDemo                 NULL
1           dbo.CustomerDemographics                 dbo.CustomerCustomerDemo
1           dbo.Customers                            dbo.CustomerCustomerDemo
1           dbo.Customers                            dbo.Orders
1           dbo.Employees                            dbo.Employees
1           dbo.Employees                            dbo.EmployeeTerritories
1           dbo.Employees                            dbo.Orders
3           dbo.EmployeeTerritories                  NULL
3           dbo.Order Details                        NULL
2           dbo.Orders                               dbo.Order Details
2           dbo.Products                             dbo.Order Details
1           dbo.Region                               dbo.Territories
1           dbo.Shippers                             dbo.Orders
1           dbo.Suppliers                            dbo.Products
2           dbo.Territories                          dbo.EmployeeTerritories

(16 row(s) affected)

Tables and Tables Referenced
 
REF_LEVEL   TABLE_NAME                               TABLE_REFERENCED                         
----------- ---------------------------------------- ---------------------------------------- 
1           dbo.Categories                           NULL
2           dbo.CustomerCustomerDemo                 dbo.CustomerDemographics
2           dbo.CustomerCustomerDemo                 dbo.Customers
1           dbo.CustomerDemographics                 NULL
1           dbo.Customers                            NULL
1           dbo.Employees                            dbo.Employees
3           dbo.EmployeeTerritories                  dbo.Employees
3           dbo.EmployeeTerritories                  dbo.Territories
3           dbo.Order Details                        dbo.Orders
3           dbo.Order Details                        dbo.Products
2           dbo.Orders                               dbo.Customers
2           dbo.Orders                               dbo.Employees
2           dbo.Orders                               dbo.Shippers
2           dbo.Products                             dbo.Categories
2           dbo.Products                             dbo.Suppliers
1           dbo.Region                               NULL
1           dbo.Shippers                             NULL
1           dbo.Suppliers                            NULL
2           dbo.Territories                          dbo.Region

(19 row(s) affected)







CODO ERGO SUM

Edited by - Michael Valentine Jones on 10/10/2006 17:14:00

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 10/03/2006 :  23:42:22  Show Profile  Visit robvolk's Homepage  Reply with Quote
Thank you Thank you THANK YOU!!! I've been wanting something like this for a while, could've SWORN I wrote it a while back but lost it and couldn't figure it out since.

BTW, did you create this just for this post? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72946

Or did you have it kicking around already?
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 10/04/2006 :  03:52:12  Show Profile  Reply with Quote
Excellent script MVJ, and much better than the fragile on we currently use internally.

Maybe a reverse sort of Output 1 for people doing a DELETE first?

And/or output dummy commands for TRUNCATE / DELETE?

Very pedantic point: but the number of levels can be reduced, given that the tables are alphabetical within their group, if child tables are included one level higher if their parent tables are alphabetically earlier. We do this because we have tables like Order, OrderItem, Customer, CustomerAddress etc. which we prefer to group adjacent (even though one level apart in RI terms) because we find it easier to consider them "at the same time" when we are making decisions about "Do I want to transfer this table" - of course for more than two levels this is little use, unless you get very creative with table names - Customer, CustomerAddress, CustomerAddressDelivery would work I suppose!

Kristen
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/04/2006 :  06:00:59  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

Thank you Thank you THANK YOU!!! I've been wanting something like this for a while, could've SWORN I wrote it a while back but lost it and couldn't figure it out since.

BTW, did you create this just for this post? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72946

Or did you have it kicking around already?


I developed it recently, not for that post.

Like you, I was sure I had one, but couldn't seem to find it. I just finished a new DB model and needed it, so I decided now was a good time to write it. Figured it would take about 15 minutes tops, but I was a little off on that estimate.




CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/04/2006 :  06:08:59  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

Excellent script MVJ, and much better than the fragile on we currently use internally.

Maybe a reverse sort of Output 1 for people doing a DELETE first?

And/or output dummy commands for TRUNCATE / DELETE?

Very pedantic point: but the number of levels can be reduced, given that the tables are alphabetical within their group, if child tables are included one level higher if their parent tables are alphabetically earlier. We do this because we have tables like Order, OrderItem, Customer, CustomerAddress etc. which we prefer to group adjacent (even though one level apart in RI terms) because we find it easier to consider them "at the same time" when we are making decisions about "Do I want to transfer this table" - of course for more than two levels this is little use, unless you get very creative with table names - Customer, CustomerAddress, CustomerAddressDelivery would work I suppose!

Kristen


I already thought about a Truncate/Delete script. It's close to the reverse, but not exactly. Maybe when I get another 15 minutes.


CODO ERGO SUM
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 10/04/2006 :  06:25:33  Show Profile  Reply with Quote
"It's close to the reverse, but not exactly"

I could do with an example that would break the "exact reverse" please - we rely on that at present!

Kristen
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/04/2006 :  08:48:11  Show Profile  Reply with Quote
Now that I’ve done the heavy lifting, it would be nice if someone developed a script that used this information to generate a DTS package to load all the tables in a database in the correct order, with proper flow-of-control, error handling, comments, and formatting, of course.

Maybe Peter could take this on when he isn’t busy helping the asshattery contingent.




CODO ERGO SUM
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 10/04/2006 :  08:59:07  Show Profile  Reply with Quote
Joking apart does it need to be DTS? or would:

INSERT INTO [TargetServer].TargetDatabase.dbo.TargetTable
SELECT * FROM [SourceServer].SourceDatabase.dbo.SourceTable

do (for each table, in the right order, of course!)

We mechanically generate the SQL scripts for "Synchronising" two databases - using R.I. ordering: a series of DELETE TargetTable for records no longer existent in the Source table, followed by (for each table) UPDATE with huge WHERE clauses comparing all columns, and INSERT for NOT EXISTS in Target database (taking into account any SET IDENTITY_INSERT stuff)

I suppose I could get off my lazy ass and make that into a suitable form for general usage ... I wonder if there would be a demand?

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 10/04/2006 :  09:14:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Got this error:
Load PK/FK references
Server: Msg 2627, Level 14, State 1, Line 53
Violation of PRIMARY KEY constraint 'PK__@r__257187A8'. Cannot insert duplicate key in object '#247D636F'.
The statement has been terminated.
Adding DISTINCT to
print 'Load PK/FK references'
insert into @r
select DISTINCT
	PK_TABLE = 
	b.TABLE_SCHEMA+'.'+b.TABLE_NAME,
	FK_TABLE = 
	c.TABLE_SCHEMA+'.'+c.TABLE_NAME
from
	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
	join
	INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
	on
	a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and
	a.UNIQUE_CONSTRAINT_NAME = b.CONSTRAINT_NAME
	join
	INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
	on
	a.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA and
	a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
order by
	1,2
helped.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 10/04/2006 09:15:14
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/04/2006 :  09:28:20  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Beautiful Michael ! thank you !!

- Jeff
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/04/2006 :  10:43:21  Show Profile  Reply with Quote
quote:
Originally posted by Kristen
Joking apart does it need to be DTS?...

It would be nice to be able to load tables in parallel where that is possible, and DTS is very good for that sort of thing. Also, you don't have to worry about linked servers.

We have done some work with generating DTS packages dynamically from VB script tasks, but nothing quite as elaborate a what I mentioned. Mostly things like: Create a package object, add source and target data sources, add a data pump task, execute the task, and get rid of the package object. It's a useful technique for "table-driven" DTS.




CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/04/2006 :  10:48:42  Show Profile  Reply with Quote
quote:
Originally posted by Peso
Got this error...

Thanks, Peter. I had just found that error myself. It occurs when a table has multiple references to another table.

I will revise the script and repost it later. I'm making some other changes, and I want to wait to see if any other errors show up.





CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/04/2006 :  11:02:33  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

"It's close to the reverse, but not exactly"

I could do with an example that would break the "exact reverse" please - we rely on that at present!

Kristen


Exact reverse will work, it just isn't exactly ideal.

For example, the tables that are level 0 (no references either way) in this script will still be level 0, and can be truncated. The next level would be tables that only reference other tables but are not referenced; these can also be truncated. After that, each additional level would be tables that are referenced by lower levels only; the data in these must be deleted.




CODO ERGO SUM
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 10/04/2006 :  12:03:09  Show Profile  Reply with Quote
Ah, got you. Good point about whether you can TRUNCATE or DELETE. I'm normally not that fussy any TRUNCATE everything, allowing those to fail that will fail, and then keeping running the DELETEs until I get no FK errors - but that's obviously a bit "rough and ready" !

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 10/04/2006 :  12:26:54  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Kristen

Ah, got you. Good point about whether you can TRUNCATE or DELETE. I'm normally not that fussy any TRUNCATE everything, allowing those to fail that will fail, and then keeping running the DELETEs until I get no FK errors - but that's obviously a bit "rough and ready" !

Kristen



http://sqlteam.com/forums/topic.asp?TOPIC_ID=65341

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 10/04/2006 :  15:03:50  Show Profile  Reply with Quote
Great, thanks! could have sworn I had something similar in a recursive tvf.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/04/2006 :  16:19:45  Show Profile  Reply with Quote
quote:
Originally posted by rockmoose

Great, thanks! could have sworn I had something similar in a recursive tvf.



What's a "recursive tvf"?


CODO ERGO SUM
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 10/04/2006 :  16:44:10  Show Profile  Reply with Quote
I like it Michael.

It uses a slightly different technique from my the method I use..

http://weblogs.sqlteam.com/davidm/archive/2005/06/17/6069.aspx

Here is the output from a db of mine..

MVJ

0 31
1 45
2 71
3 76
4 20
5 5
6 1

and mine

0 76
1 71
2 76
3 20
4 5
5 1

Notice the "down shifted" levels...



DavidM

Production is just another testing cycle
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 10/04/2006 :  17:03:03  Show Profile  Reply with Quote
quote:
Originally posted by rockmoose

Great, thanks! could have sworn I had something similar in a recursive tvf.


Example:
create function dbo.rectvf(@nestlvl int)
returns @nest table
(
	nest int
)
as
begin
	if @nestlvl = 0
		return
	insert @nest select @nestlvl
	set @nestlvl = @nestlvl -1
	insert @nest select nest from dbo.rectvf(@nestlvl)
	return
end
go

select * From dbo.rectvf(6)

nest        
----------- 
6
5
4
3
2
1

(6 row(s) affected)


rockmoose
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 10/05/2006 :  04:50:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by Michael Valentine Jones

Maybe Peter could take this on when he isn’t busy helping the asshattery contingent.
OK. You asked for it

Here is a slightly different approach, which gets the database diagram as a resultset, ready to be put in any client application grid control.
Or any ASP page for that matter...
SET NOCOUNT ON

DECLARE	@Constraints TABLE
	(
		ConstraintID SMALLINT IDENTITY(0, 1),
		UNIQUE_CONSTRAINT_CATALOG NVARCHAR(128),
		UNIQUE_CONSTRAINT_SCHEMA NVARCHAR(128),
		UNIQUE_CONSTRAINT_NAME NVARCHAR(128),
		CONSTRAINT_CATALOG NVARCHAR(128),
		CONSTRAINT_SCHEMA NVARCHAR(128),
		CONSTRAINT_NAME NVARCHAR(128),
		TABLE_CATALOG NVARCHAR(128),
		TABLE_SCHEMA NVARCHAR(128),
		TABLE_NAME NVARCHAR(128),
		COLUMN_NAME NVARCHAR(128),
		DATA_TYPE NVARCHAR(128)
	)

INSERT		@Constraints
		(
			UNIQUE_CONSTRAINT_CATALOG,
			UNIQUE_CONSTRAINT_SCHEMA,
			UNIQUE_CONSTRAINT_NAME,
			CONSTRAINT_CATALOG,
			CONSTRAINT_SCHEMA,
			CONSTRAINT_NAME,
			TABLE_CATALOG,
			TABLE_SCHEMA,
			TABLE_NAME,
			COLUMN_NAME,
			DATA_TYPE
		)
SELECT		rc.UNIQUE_CONSTRAINT_CATALOG,
		rc.UNIQUE_CONSTRAINT_SCHEMA,
		rc.UNIQUE_CONSTRAINT_NAME,
		tc.CONSTRAINT_CATALOG,
		tc.CONSTRAINT_SCHEMA,
		tc.CONSTRAINT_NAME,
		kcu.TABLE_CATALOG,
		kcu.TABLE_SCHEMA,
		kcu.TABLE_NAME,
		c.COLUMN_NAME,
		c.DATA_TYPE
FROM		INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN	INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON kcu.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
			AND kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
			AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
INNER JOIN	INFORMATION_SCHEMA.COLUMNS c ON c.TABLE_CATALOG = kcu.TABLE_CATALOG
			AND c.TABLE_SCHEMA = kcu.TABLE_SCHEMA
			AND c.TABLE_NAME = kcu.TABLE_NAME
			AND c.COLUMN_NAME = kcu.COLUMN_NAME
LEFT JOIN	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON rc.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
			AND rc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
			AND rc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE		OBJECTPROPERTY(OBJECT_ID(tc.TABLE_NAME), 'IsMSShipped') = 0
		AND tc.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY')

DECLARE	@Tables TABLE
	(
		UNIQUE_CONSTRAINT_CATALOG NVARCHAR(128),
		UNIQUE_CONSTRAINT_SCHEMA NVARCHAR(128),
		UNIQUE_CONSTRAINT_NAME NVARCHAR(128),
		CONSTRAINT_CATALOG NVARCHAR(128),
		CONSTRAINT_SCHEMA NVARCHAR(128),
		CONSTRAINT_NAME NVARCHAR(128),
		TABLE_CATALOG NVARCHAR(128),
		TABLE_SCHEMA NVARCHAR(128),
		TABLE_NAME NVARCHAR(128),
		COLUMN_NAME NVARCHAR(128),
		DATA_TYPE NVARCHAR(128)
	)

INSERT		@Tables
		(
			UNIQUE_CONSTRAINT_CATALOG,
			UNIQUE_CONSTRAINT_SCHEMA,
			UNIQUE_CONSTRAINT_NAME,
			CONSTRAINT_CATALOG,
			CONSTRAINT_SCHEMA,
			CONSTRAINT_NAME,
			TABLE_CATALOG,
			TABLE_SCHEMA,
			TABLE_NAME,
			COLUMN_NAME,
			DATA_TYPE
		)
SELECT		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		TABLE_CATALOG,
		TABLE_SCHEMA,
		TABLE_NAME,
		NULL,
		NULL
FROM		INFORMATION_SCHEMA.TABLES
WHERE		OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0
		AND TABLE_TYPE = 'BASE TABLE'

DELETE		t
FROM		@Tables t
INNER JOIN	@Constraints c ON t.TABLE_CATALOG = c.TABLE_CATALOG
			AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
			AND t.TABLE_NAME = c.TABLE_NAME

DECLARE	@Tree TABLE
	(
		RowID SMALLINT IDENTITY(0, 1),
		RowKey VARBINARY(5966),
		Generation SMALLINT,
		ConstraintID SMALLINT,
		CONSTRAINT_CATALOG NVARCHAR(128),
		CONSTRAINT_SCHEMA NVARCHAR(128),
		CONSTRAINT_NAME NVARCHAR(128),
		TABLE_CATALOG NVARCHAR(128),
		TABLE_SCHEMA NVARCHAR(128),
		TABLE_NAME NVARCHAR(128),
		COLUMN_NAME NVARCHAR(128),
		DATA_TYPE NVARCHAR(128)
	)

INSERT		@Tree
		(
			Generation,
			ConstraintID,
			CONSTRAINT_CATALOG,
			CONSTRAINT_SCHEMA,
			CONSTRAINT_NAME,
			TABLE_CATALOG,
			TABLE_SCHEMA,
			TABLE_NAME,
			COLUMN_NAME,
			DATA_TYPE
		)
SELECT		0,
		ConstraintID,
		CONSTRAINT_CATALOG,
		CONSTRAINT_SCHEMA,
		CONSTRAINT_NAME,
		TABLE_CATALOG,
		TABLE_SCHEMA,
		TABLE_NAME,
		COLUMN_NAME,
		DATA_TYPE
FROM		@Constraints
WHERE		UNIQUE_CONSTRAINT_CATALOG IS NULL
		AND UNIQUE_CONSTRAINT_SCHEMA IS NULL
		AND UNIQUE_CONSTRAINT_NAME IS NULL
UNION
SELECT		0,
		NULL,
		CONSTRAINT_CATALOG,
		CONSTRAINT_SCHEMA,
		CONSTRAINT_NAME,
		TABLE_CATALOG,
		TABLE_SCHEMA,
		TABLE_NAME,
		COLUMN_NAME,
		DATA_TYPE
FROM		@Tables
ORDER BY	TABLE_CATALOG,
		TABLE_SCHEMA,
		TABLE_NAME,
		COLUMN_NAME

DELETE		t
FROM		@Tree t
INNER JOIN	@Constraints c ON c.TABLE_CATALOG = t.TABLE_CATALOG
			AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
			AND c.TABLE_NAME = t.TABLE_NAME
			and c.UNIQUE_CONSTRAINT_CATALOG IS NOT NULL
			AND c.UNIQUE_CONSTRAINT_SCHEMA IS NOT NULL
			AND c.UNIQUE_CONSTRAINT_NAME IS NOT NULL
INNER JOIN	@Tree x ON x.CONSTRAINT_CATALOG = c.UNIQUE_CONSTRAINT_CATALOG
			AND x.CONSTRAINT_SCHEMA = c.UNIQUE_CONSTRAINT_SCHEMA
			AND x.CONSTRAINT_NAME = c.UNIQUE_CONSTRAINT_NAME
			AND x.TABLE_CATALOG = t.TABLE_CATALOG
			AND x.TABLE_SCHEMA = t.TABLE_SCHEMA
			AND x.TABLE_NAME <> t.TABLE_NAME

DELETE		c
FROM		@Constraints c
INNER JOIN	@Tree t ON t.ConstraintID = c.ConstraintID

UPDATE	@Tree
SET	RowKey = CAST(RowID AS VARBINARY)

DECLARE	@Generation SMALLINT

SELECT	@Generation = 0

WHILE @@ROWCOUNT > 0
	BEGIN
		SELECT	@Generation = @Generation + 1		

		INSERT		@Tree
				(
					RowKey,
					Generation,
					ConstraintID,
					CONSTRAINT_CATALOG,
					CONSTRAINT_SCHEMA,
					CONSTRAINT_NAME,
					TABLE_CATALOG,
					TABLE_SCHEMA,
					TABLE_NAME,
					COLUMN_NAME,
					DATA_TYPE
				)
		SELECT		t.RowKey,
				@Generation,
				c.ConstraintID,
				c.CONSTRAINT_CATALOG,
				c.CONSTRAINT_SCHEMA,
				c.CONSTRAINT_NAME,
				c.TABLE_CATALOG,
				c.TABLE_SCHEMA,
				c.TABLE_NAME,
				c.COLUMN_NAME,
				c.DATA_TYPE
		FROM		@Constraints c
		INNER JOIN	(
					SELECT	RowKey,
						CONSTRAINT_CATALOG,
						CONSTRAINT_SCHEMA,
						CONSTRAINT_NAME
					FROM	@Tree
					WHERE	Generation = @Generation - 1
				) t ON t.CONSTRAINT_CATALOG = c.UNIQUE_CONSTRAINT_CATALOG
					AND t.CONSTRAINT_SCHEMA = c.UNIQUE_CONSTRAINT_SCHEMA
					AND t.CONSTRAINT_NAME = c.UNIQUE_CONSTRAINT_NAME
		ORDER BY	c.TABLE_CATALOG,
				c.TABLE_SCHEMA,
				c.TABLE_NAME,
				c.COLUMN_NAME

		UPDATE	@Tree
		SET	RowKey = RowKey + CAST(RowID AS VARBINARY)
		WHERE	Generation = @Generation

		UPDATE		t
		SET		t.ConstraintID = c.ConstraintID,
				t.CONSTRAINT_CATALOG = c.CONSTRAINT_CATALOG,
				t.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA,
				t.CONSTRAINT_NAME = c.CONSTRAINT_NAME
		FROM		@Tree t
		INNER JOIN	@Constraints c ON c.TABLE_CATALOG = t.TABLE_CATALOG
					AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
					AND c.TABLE_NAME = t.TABLE_NAME
		WHERE		t.Generation = @Generation
				AND c.UNIQUE_CONSTRAINT_CATALOG IS NULL
				AND c.UNIQUE_CONSTRAINT_SCHEMA IS NULL
				AND c.UNIQUE_CONSTRAINT_NAME IS NULL

		DELETE		c
		FROM		@Constraints c
		INNER JOIN	@Tree t ON t.ConstraintID = c.ConstraintID
	END

SELECT		Generation [Level],
		TABLE_CATALOG,
		TABLE_SCHEMA,
		TABLE_NAME,
		COLUMN_NAME,
		DATA_TYPE
FROM		@Tree
ORDER BY	RowKey

Peter Larsson
Helsingborg, Sweden

EDIT: Added code to include not referenced tables

Edited by - SwePeso on 10/05/2006 05:21:57
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 10/05/2006 :  05:07:53  Show Profile  Reply with Quote
Can it be optimised to run a bit faster please Peso? I can't get it to complete the first step ... those pesky INFORMATION_SCHEMA views
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000