My project is getting ready to roll, so data versioning has commenced.. Backup, restore, BCP etc... Digging around in the toolkit, I dredge up an old procedure I wrote for load/unload in version 7.  It didn't have the “owner” of the table and avoided circular references.  So I set out to bring it up to speed ...

It should work in both 2000 and 2005 (and 7 when turned into a proc). Can someone give it a whirl on 2005 and tell me how it goes ? 'Cause if they have messed with the INFORMATION_SCHEMA views I 'll.........#%#%#$#$%................

CREATE FUNCTION RISequence()
RETURNS @RISEQUENCE TABLE (TABLE_NAME SYSNAME, PRIORITY SMALLINT NOT NULL, PRIMARY KEY(TABLE_NAME))
AS
--Returns The user tables and the "depth" of the table in the RI chain.
--Tables with no FK's are given a PRIORITY of 0
--Circular reference tables have a minimum PRIORITY of 1
BEGIN
--COUNTER
DECLARE @COUNTER INT, @PRIORITY INT
--RI Table Matching
DECLARE @RIMATCH TABLE (PK_TABLE SYSNAME NOT NULL, FK_TABLE SYSNAME NOT NULL)
--Populate @RIMATCH -- Remove Self referenced entities (WHERE expression)
INSERT @RIMATCH(PK_TABLE, FK_TABLE)
SELECT PK.TABLE_SCHEMA + '.' + PK.TABLE_NAME  AS PK_TABLE
 , FK.TABLE_SCHEMA + '.' + FK.TABLE_NAME AS FK_TABLE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS FK ON FK.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS PK ON PK.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
WHERE FK.TABLE_SCHEMA + FK.TABLE_NAME != PK.TABLE_SCHEMA + PK.TABLE_NAME

--No FK's (PRIORITY=0)
SET @PRIORITY = 0
INSERT @RISequence(TABLE_NAME, PRIORITY)
 SELECT TABLE_SCHEMA + '.' + TABLE_NAME, @PRIORITY
 FROM INFORMATION_SCHEMA.TABLES AS T
 WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME != 'dtproperties'
  AND NOT EXISTS (SELECT 1 FROM @RIMATCH C WHERE C.FK_TABLE = T.TABLE_SCHEMA + '.' + T.TABLE_NAME)

--Circular Reference (CR) PRIORITY=1 ie The only FK is the circular FK
SET @PRIORITY = @PRIORITY + 1
INSERT @RISequence(TABLE_NAME, PRIORITY)
 SELECT S.FK_TABLE, @PRIORITY 
 FROM @RIMATCH S
 WHERE EXISTS (SELECT 1 FROM @RIMATCH X WHERE X.PK_TABLE = S.FK_TABLE AND X.FK_TABLE = S.PK_TABLE)
  AND NOT EXISTS (SELECT 1 FROM @RISequence C WHERE C.TABLE_NAME = S.FK_TABLE)
 GROUP BY S.FK_TABLE
 HAVING COUNT(*) = (SELECT COUNT(*) FROM @RIMATCH K WHERE S.FK_TABLE = K.FK_TABLE)

--Fudge a DO operation
SET @COUNTER = 1
WHILE @Counter > 0
BEGIN
 SET @COUNTER = 0
 --Standard RI
 INSERT @RISequence (TABLE_NAME, PRIORITY)
  SELECT K.FK_TABLE , @PRIORITY
  FROM @RIMATCH AS K
  INNER JOIN @RISequence O ON O.TABLE_NAME = K.PK_TABLE
  WHERE NOT EXISTS(SELECT 1 FROM @RISequence F WHERE K.FK_TABLE = F.TABLE_NAME)
  GROUP BY K.FK_TABLE
  HAVING COUNT(*) = (SELECT COUNT(*) FROM @RIMATCH S WHERE S.FK_TABLE = K.FK_TABLE)
 SET @COUNTER = CASE @@ROWCOUNT WHEN 0 THEN 0 ELSE 1 END
 
 --Because of the forced discovery of PRIORITY 1 CR, increment takes place here
 SET @PRIORITY = @PRIORITY + 1

 --Circular References with related RI at the current PRIORITY Level
 INSERT @RISequence(TABLE_NAME, PRIORITY)
  SELECT S.FK_TABLE, @PRIORITY
  FROM @RIMATCH S
  INNER JOIN @RIMATCH X ON X.PK_TABLE = S.FK_TABLE AND X.FK_TABLE = S.PK_TABLE
   WHERE NOT EXISTS (SELECT 1 FROM @RISequence W WHERE S.FK_TABLE  = W.TABLE_NAME )
  AND EXISTS (SELECT 1 FROM @RIMATCH Z WHERE S.PK_TABLE = Z.FK_TABLE
    AND Z.PK_TABLE != S.FK_TABLE
    AND EXISTS (SELECT 1 from @RISequence A WHERE Z.PK_TABLE = A.TABLE_NAME))
 SET @COUNTER = @COUNTER + CASE @@ROWCOUNT WHEN 0 THEN 0 ELSE 1 END
END
RETURN
END
GO

--In Use
SELECT * from dbo.RISequence() ORDER BY PRIORITY, TABLE_NAME
--DROP FUNCTION RISequence

Enjoy!