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
 General SQL Server Forums
 Script Library
 Find Table Reference Levels

Author  Topic 

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-03 : 22:21:59
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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-03 : 23:42:22
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

22859 Posts

Posted - 2006-10-04 : 03:52:12
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)

7020 Posts

Posted - 2006-10-04 : 06:00:59
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)

7020 Posts

Posted - 2006-10-04 : 06:08:59
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

22859 Posts

Posted - 2006-10-04 : 06:25:33
"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)

7020 Posts

Posted - 2006-10-04 : 08:48:11
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

22859 Posts

Posted - 2006-10-04 : 08:59:07
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

30421 Posts

Posted - 2006-10-04 : 09:14:15
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
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-10-04 : 09:28:20
Beautiful Michael ! thank you !!

- Jeff
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-04 : 10:43:21
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)

7020 Posts

Posted - 2006-10-04 : 10:48:42
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)

7020 Posts

Posted - 2006-10-04 : 11:02:33
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

22859 Posts

Posted - 2006-10-04 : 12:03:09
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

22864 Posts

Posted - 2006-10-04 : 12:26:54
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

3279 Posts

Posted - 2006-10-04 : 15:03:50
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)

7020 Posts

Posted - 2006-10-04 : 16:19:45
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

1591 Posts

Posted - 2006-10-04 : 16:44:10
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

3279 Posts

Posted - 2006-10-04 : 17:03:03
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

30421 Posts

Posted - 2006-10-05 : 04:50:12
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
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-10-05 : 05:07:53
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
    Next Page

- Advertisement -