SQL Server: Find Table Order/Generation And Relation Details

• 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