Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there any way in SQL Server 2000 to get a list of tables ordered by dependency? I need it for insert order in empty data base.

I can't turn off constraints, so I'm stuck with this solution.

Thanks

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

I think this function is a good starting point. The original source is lost in the sands of time, but I picked it up from Uri Dimant.

Normally I don't copy linked content wholesale, but this looks like it will benefit everyone by being here (I've edited a few typos).


Some time ago I was asked to truncate lots of tables in client's database. As such, all those tables have FK and PK dependency. You get the error like that if you run TRUNCATE TABLE Categories in Northwind database as an example.

--Cannot truncate table 'Categories' because it is being referenced by a FOREIGN KEY constraint.

So , there is no such great problem as you know the database structure and can easily indentify parent-child tables. But what if you don't? I'd like to show the function (sadly, I don't remember by whom it is written) that returns the tables ordered by its level. Level 0 means that it is a referenced table and should be truncated last.

CREATE VIEW VFKs
AS

SELECT
FK.TABLE_SCHEMA AS child_table_schema,
FK.TABLE_NAME AS child_table_name,
PK.TABLE_SCHEMA AS parent_table_schema,
PK.TABLE_NAME AS parent_table_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON RC.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON RC.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
GO

CREATE FUNCTION dbo.fn_get_tree_of_tables()
RETURNS @tree TABLE
(
table_schema SYSNAME NOT NULL,
table_name SYSNAME NOT NULL,
lvl INT NOT NULL,
PRIMARY KEY(table_schema, table_name)
)
AS
BEGIN

DECLARE @lvl AS INT
SET @lvl = 0

-- top level tables
INSERT INTO @tree
SELECT DISTINCT
parent_table_schema,
parent_table_name,
@lvl
FROM VFKs AS P
WHERE NOT EXISTS
(SELECT *
FROM VFKs AS C
WHERE C.child_table_schema = P.parent_table_schema
AND C.child_table_name = P.parent_table_name)

WHILE @@rowcount > 0
BEGIN

SET @lvl = @lvl + 1

-- non top level tables
INSERT INTO @tree
SELECT DISTINCT
child_table_schema,
child_table_name,
@lvl
FROM VFKs AS C
JOIN @tree AS P
ON P.table_schema = C.parent_table_schema
AND P.table_name = C.parent_table_name
AND lvl = @lvl - 1
WHERE NOT EXISTS
(SELECT *
FROM @tree AS T
WHERE T.table_schema = C.child_table_schema
AND T.table_name = C.child_table_name)

END

SET @lvl = 0

-- tables with no fks
INSERT INTO @tree
SELECT TABLE_SCHEMA, TABLE_NAME, @lvl
FROM INFORMATION_SCHEMA.TABLES AS TB
WHERE NOT EXISTS(
SELECT *
FROM @tree AS TR
WHERE TB.TABLE_SCHEMA = TR.table_schema
AND TB.TABLE_NAME = TR.table_name)
AND
TB.TABLE_TYPE = 'BASE TABLE'

RETURN

END
GO

Here's the results of an invocation of the function in Northwind:

SELECT * FROM dbo.fn_get_tree_of_tables() AS T
ORDER BY lvl

table_schema table_name lvl
------------ ----------------------------------- ---
dbo Categories 0
dbo CustomerDemographics 0
dbo Customers 0
dbo Employees 0
dbo Region 0
dbo Shippers 0
dbo Suppliers 0
dbo Products 1
dbo Territories 1
dbo Orders 1
dbo CustomerCustomerDemo 1
dbo Order Details 2
dbo EmployeeTerritories 2
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.