Script to generate table dependecny in order

Question

Hi All,

I need urgent help. My project is to load data from source to target. The target has RI defined, so I need to load the data in order or RI. So I'd like to start from the top most table and wrok my way down. I need the data returned in the following format:

Dependency Order No., Table Name

So far I've the following base script:

SELECT
        dependent_table = OBJECT_NAME(FKeyID),
        parent_table = OBJECT_NAME(RKeyID),
        link = OBJECT_NAME(FKeyID) + '.' + (
                                                SELECT
                                                        C.Name
                                                FROM
                                                        Sys.syscolumns C
                                                WHERE
                                                        C.ID = FKeyID
                                                        AND C.ColID = FKey
                                              ) + ' = ' + OBJECT_NAME(RKeyID)
        + '.' + (
                  SELECT
                        C.Name
                  FROM
                        Sys.syscolumns C
                  WHERE
                        C.ID = RKeyID
                        AND C.ColID = RKey
                )
FROM
        sys.sysforeignkeys

Thanks in advance!

---

GBM

Answer 1

This is not generally possible, as there may be circular constraints.  Also it's easier to find and NOCHECK all the constraints before the load, and then CHECK them at the end.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 2

Recursive CTE to the rescue! Please see if the below script works for you. You will want to delete data from the destination in DESC order of the LevelNumber (i.e. delete child tables before parent tables) and load data in ASC order of the LevelNumber (i.e. load parent tables before child tables).

WITH 
CteForData
AS (
SELECT s.name                              AS SchemaName, 
       o.name                              AS TableName, 
       /* self referencing tables have same parent 
       and child table names. Make it NULL */
       NULLIF(OBJECT_NAME(rkeyid), o.name) AS ParentTable, 
       QUOTENAME(OBJECT_NAME(fkeyid)) 
       + '.' 
       + (SELECT QUOTENAME(c.name) 
          FROM   sys.syscolumns AS c 
          WHERE  c.id = fkeyid 
                 AND c.colid = fkey) 
       + ' = ' 
       + QUOTENAME(OBJECT_NAME(rkeyid)) 
       + '.' 
       + (SELECT QUOTENAME(c.name) 
          FROM   sys.syscolumns AS c 
          WHERE  c.id = rkeyid 
                 AND c.colid = rkey)       AS LinkingInformation 
FROM   sys.objects AS o 
       INNER JOIN sys.schemas AS s 
               ON o.schema_id = s.schema_id 
       LEFT JOIN sys.sysforeignkeys AS f 
              ON o.object_id = f.fkeyid 
WHERE  o.type = 'U' 
 ), 
CteForHierarchy 
AS (
	SELECT 1 AS LevelNumber, 
		   m.SchemaName, 
		   m.TableName, 
		   m.ParentTable, 
		   m.LinkingInformation 
	FROM   CteForData AS m 
	WHERE  m.ParentTable IS NULL 
	UNION ALL 
	SELECT a.LevelNumber + 1 AS LevelNumber, 
		   b.SchemaName, 
		   b.TableName, 
		   b.ParentTable, 
		   b.LinkingInformation 
	FROM   CteForHierarchy AS a 
		   INNER JOIN CteForData AS b 
				   ON a.TableName = b.ParentTable 
) 
SELECT c.LevelNumber, 
       c.SchemaName, 
       c.TableName, 
       c.ParentTable, 
       c.LinkingInformation, 
       'DELETE FROM ' 
		   + QUOTENAME(c.SchemaName) 
		   + '.' 
		   + QUOTENAME(c.TableName) AS DeleteCommand 
FROM   CteForHierarchy AS c 
		/* Just show the maximum level number */
       INNER JOIN (SELECT SchemaName, 
                          TableName, 
                          MAX(LevelNumber) AS LevelNumber 
                   FROM   CteForHierarchy 
                   GROUP  BY SchemaName, 
                             TableName) AS m 
               ON c.SchemaName = m.SchemaName 
                  AND c.TableName = m.TableName 
                  AND c.LevelNumber = m.LevelNumber 
ORDER  BY c.LevelNumber, 
          c.SchemaName, 
          c.TableName, 
          c.ParentTable 

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)

Answer 3

Below is a script. However, as David says, circular references are not unlikely. My script produces two result sets: one with the load order, one with tables that are entangled in circular references, or dependent tables that are. When I run it in a database with a complex data model, about one-third of the tables end up in the second result set.

A better approach is

  SELECT 'ALTER TABLE ' + quotename(name) + ' NOCHECK CONSTRAINT ALL'
  FROM   sys.tables
  WHERE  type = 'U'

Copy, paste and run result.

and when you are done run

  SELECT 'ALTER TABLE ' + quotename(name) + ' WITH CHECK CHECK CONSTRAINT ALL'
  FROM   sys.tables
  WHERE  type = 'U'

Important to have WITH CHECK CHECK, or else the constraints will not be trusted. If you get errors, DBCC CHECKCONSTRAINTS can help you to find them.

Here is my script:

CREATE TABLE  #temp (name sysname NOT NULL UNIQUE,
                     object_id int NOT NULL PRIMARY KEY,
                     lvl       tinyint NOT NULL)

DECLARE @rowc int,
        @lvl  tinyint

INSERT #temp (name, object_id, lvl)
   SELECT t.name, t.object_id, 1
   FROM   sys.tables t
   WHERE  NOT EXISTS (SELECT *
                      FROM   sys.foreign_keys fk
                      WHERE  fk.parent_object_id = t.object_id
                        AND  fk.parent_object_id <> fk.referenced_object_id)
SELECT @rowc = @@rowcount

SELECT @lvl = 2

WHILE @rowc > 0
BEGIN
  INSERT #temp (name, object_id, lvl)
     SELECT t.name, t.object_id, @lvl
     FROM   sys.tables t
     WHERE  EXISTS (SELECT *
                    FROM   #temp tmp
                    JOIN   sys.foreign_keys fk ON tmp.object_id = fk.referenced_object_id
                    WHERE  fk.parent_object_id = t.object_id)
       AND  NOT EXISTS (SELECT *
                        FROM   sys.foreign_keys fk
                        WHERE  fk.parent_object_id = t.object_id
                          AND  fk.parent_object_id <> fk.referenced_object_id
                          AND  NOT EXISTS (SELECT *
                                           FROM   #temp tmp
                                           WHERE  tmp.object_id = fk.referenced_object_id))
       AND  NOT EXISTS (SELECT *
                        FROM   #temp tmp
                        WHERE  t.object_id = tmp.object_id)
  SELECT @rowc = @@rowcount, @lvl = @lvl + 1
END

SELECT * FROM #temp ORDER BY lvl, name

SELECT t.name
FROM   sys.tables t
WHERE  NOT EXISTS (SELECT * FROM #temp tmp WHERE t.object_id = tmp.object_id)
ORDER BY t.name
go
DROP TABLE #temp

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

What I normally do is

1. disable the foreign keys and triggers
2. import the data
3. enable the foreign keys and triggers
4. perform a check on the foreign keys to make them trusted again (do not forget this or it will cost you performance)
5. list the foreign keys that are still not trusted and repair the data manually

Answer 5

Here are the statements to generate the statements to do the above. If you use those statements in a cursor and execute the generated statements as dynamic SQL, you can automate the process.

-- Generate statements to disable all foreign keys
SELECT 'ALTER TABLE ' + OBJECT_NAME(parent_object_id) + ' NOCHECK CONSTRAINT ' + name  FROM sys.foreign_keys 
GO
-- Generate statements to disable all triggers
SELECT 'ALTER  TABLE ' + OBJECT_NAME(parent_id) + ' DISABLE TRIGGER ' + name FROM sys.triggers;
GO
-- Generate statements to enable all triggers
SELECT 'ALTER  TABLE ' + OBJECT_NAME(parent_id) + ' ENABLE TRIGGER ' + name FROM sys.triggers;
GO
-- Generate statements to enable all foreign keys
SELECT 'ALTER TABLE ' + OBJECT_NAME(parent_object_id) + ' CHECK CONSTRAINT ' + name  FROM sys.foreign_keys 
GO
-- Generate statements to check  all foreign keys
SELECT 'ALTER TABLE ' + OBJECT_NAME(parent_object_id) + ' WITH CHECK CHECK CONSTRAINT ' + name  FROM sys.foreign_keys 
GO
-- List the foreign keys that still are not trusted
SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1
GO

Answer 6

There was an error in my previous post. I had to use the "parent_id" in the trigger statements, not the "object_id". I just repaired that.

And just in case you do not know how to use cursors or dynamic sql, here is one example.

DECLARE @statement varchar(1000);
DECLARE cr_Disable_Cursor CURSOR LOCAL STATIC FOR
    SELECT 'ALTER  TABLE ' + OBJECT_NAME(parent_id) + ' DISABLE TRIGGER ' + name FROM sys.triggers;
OPEN cr_Disable_Cursor;
FETCH cr_Disable_Cursor INTO @statement;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Statement;
    EXECUTE(@statement);
    FETCH cr_Disable_Cursor INTO @statement;
END;
CLOSE cr_Disable_Cursor;
DEALLOCATE cr_Disable_Cursor;
GO

Answer 7

Recursive CTE to the rescue! Please see if the below script works for you. You will want to delete data from the destination in DESC order of the LevelNumber (i.e. delete child tables before parent tables) and load data in ASC order of the LevelNumber (i.e. load parent tables before child tables).

Try the script in msdb for a nasty surprise...

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

First of all, thanks a lot Aalam and Chris for putting the effort. Unfortunately as Erland experienced, Aalam's script didn't work on my DB, it just kept executing and never came back.

Chris, I've used a similar technique that you suggested in the past, but this particular problem is more complex than just simply disabling and enabling the constraints and triggers.

Erland, thanks a lot for suggesting the script. I've followed the tips on your website many a times. 

---

GBM