-- 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

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)

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?

---

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

---

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.

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

quote:

---

Originally posted by Kristen
Joking apart does it need to be DTS?...

---

quote:

---

Originally posted by Peso
Got this error...

---

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

---

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

---

quote:

---

Originally posted by rockmoose

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

---

quote:

---

Originally posted by rockmoose

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

---

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)

quote:

---

Originally posted by Michael Valentine Jones

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

---

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