SQL Server 2000 : List of tables ordered from by number of referenced tables 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
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 &gt; 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