Download source code - 2.6 KB

Introduction

While working with a relational database, I came into a point to find the relations between tables. The aim was to clean tables in proper order and sync data from another database, which lead me to a scenario to find table generation order.

Background

What are we going to do here?

Find the relation between tables.
Find the generation of each table.
- Find first generation tables (tables having no foreign key, or it's own column as a foreign key).
- Find the generation of remaining tables.

Temporary Tables

IF object_id('tempdb..#tblRelation') is not null
    DROP TABLE #tblRelation;
IF object_id('tempdb..#tblDetail') is not null
    DROP TABLE #tblDetail;

/*table relation details*/
CREATE TABLE #tblRelation (ObjectId VARCHAR(100) NOT NULL,
                            Name VARCHAR(100) NOT NULL,
                            [Schema] VARCHAR(100) NOT NULL,
                            [Column] VARCHAR(100) NOT NULL,
                            FkFromObjectId VARCHAR(100) NOT NULL,                            
                            FkFromTbl VARCHAR(100) NOT NULL,
                            FkFromSchema VARCHAR(100) NOT NULL,
                            FkFromClm VARCHAR(100) NOT NULL);

/*table generation details*/
CREATE TABLE #tblDetail (Name VARCHAR(100) NOT NULL,
                         [Schema] VARCHAR(100) NOT NULL,
                         Generation INT NULL);

Find Table Relation Details

Here, we are gathering relation between tables:

INSERT
    INTO #tblRelation
    SELECT
        parent_object_id,
        OBJECT_NAME(parent_object_id),
        OBJECT_SCHEMA_NAME(parent_object_id),
        c.NAME,
        referenced_object_id,
        OBJECT_NAME(referenced_object_id),
        OBJECT_SCHEMA_NAME(referenced_object_id),
        cref.NAME
    FROM 
        sys.foreign_key_columns fk
    INNER JOIN 
        sys.columns c 
           ON fk.parent_column_id = c.column_id 
              AND fk.parent_object_id = c.object_id
    INNER JOIN 
        sys.columns cref 
           ON fk.referenced_column_id = cref.column_id 
              AND fk.referenced_object_id = cref.object_id;

Find Table Generation Details

Now, it's time to find table generations using table relation data.

Find First Generation Tables

What is the first generation table?

Tables having no foreign key
Or only it's own column as a foreign key

/*find first generation table*/
INSERT 
    INTO #tblDetail(Name, [Schema], Generation)
    (SELECT 
        TABLE_NAME, 
        TABLE_SCHEMA,
        (CASE 
            WHEN(
            /*if tbl has no fk, first generation tbl, 0*/
            (SELECT COUNT(*) 
                FROM #tblRelation
                WHERE Name = TABLE_NAME AND [Schema] = TABLE_SCHEMA) = 0
            )
            THEN(SELECT 0)
            
            WHEN(
            /*if tbl has fk, but all of them from his own columns, 
              first generation tbl, 0*/
            (SELECT COUNT(*) 
                FROM #tblRelation
                WHERE Name = TABLE_NAME AND [Schema] = TABLE_SCHEMA) = 
            (SELECT COUNT(*) 
                FROM #tblRelation
                WHERE Name = TABLE_NAME
                    AND FkFromTbl = TABLE_NAME  AND [Schema] = TABLE_SCHEMA)        
            )
            THEN(SELECT 0)
            
            /*tbl has fk, from other tbl columns, NULL*/
            ELSE(SELECT NULL)
          END)    
        FROM INFORMATION_SCHEMA.TABLES 
            WHERE TABLE_TYPE = 'BASE TABLE'
            AND TABLE_NAME != 'sysdiagrams');

Find the Generation of Remaining Tables

Now let find the 1, 2, 3, ..... N^th generation tables.

/*set new generation using olds*/
WHILE(EXISTS(SELECT * 
                FROM #tblDetail 
                WHERE Generation IS NULL))
BEGIN

    /*find new generation tables*/
    DECLARE @tblNewGeneration TABLE(Name VARCHAR(100) NOT NULL,
                                    [Schema] VARCHAR(100) NOT NULL)                                
    /*Select tables where all of its foreign key table’s generation were found*/                            
    INSERT INTO @tblNewGeneration
    SELECT Name, [Schema]
        FROM #tblDetail AS dtl
        WHERE Generation IS NULL
        AND NOT EXISTS (
                SELECT DISTINCT FkFromTbl,FkFromSchema
                    FROM #tblRelation AS rel
                    WHERE rel.Name = dtl.Name 
                    AND rel.[Schema] = dtl.[Schema] 
                EXCEPT
                SELECT Name, [Schema]
                    FROM #tblDetail
                    WHERE Generation IS NOT NULL
        )

    /*set generation*/
    DECLARE @crntGeneration INT
    SET @crntGeneration = (SELECT MAX(Generation)  
                            FROM #tblDetail
                            WHERE Generation IS NOT NULL);                            
    UPDATE #tblDetail
        SET Generation = @crntGeneration + 1
        WHERE [Schema]+'.'+Name 
        IN (
            SELECT [Schema]+'.'+Name
                FROM @tblNewGeneration
        )
    
    /*clean the new generation tbls from temp*/    
    DELETE FROM @tblNewGeneration;
END;

Results

Table Generations

/*table generations*/
SELECT *
    FROM #tblDetail
    ORDER BY Generation, Name, [Schema];

Table Relations

/*table relations*/
SELECT *
    FROM #tblRelation
    ORDER BY Name, [Schema], FkFromTbl, FkFromSchema;

Unwanted Scenario

We may also find NULL as a generation or order value. It means there is a present of Circular Relations in the database and it is essential to redefine the table relations.

Limitation

The solution may vary depending on the database version. Our tested databases are

SQL Server-2008R2
SQL Server-2012
SQL Server-2014
SQL Server-2016

Please find the necessary SQL files as an attachment.

History

29^th May, 2019: Initial version

SQL Server: Find Table Order/Generation And Relation Details