Ordering Tables To Preserve Referential Integrity

  • Glen Cooper

    SSCommitted

    Points: 1696

    Comments posted to this topic are about the item Ordering Tables To Preserve Referential Integrity

    R Glen Cooper

  • John Muir

    SSC Enthusiast

    Points: 124

    Could someone fix the table? Its got <tr&gt's instead of <td&gt's

  • JJ B

    SSCarpal Tunnel

    Points: 4572

    Awesome article and very helpful!!! I haven't had a chance to go through the code yet, but the description of what you are trying to accomplish is very clear.

    I'm excited because I think I might be able to use this code in the future. If I am making major changes to a database, I will completely re-engineer the database from scratch for both development purposes and later as part of the rollout process. What I mean by "completely re-engineer..." is that I follow these steps (which happens about 1 or 2 times a year for the major databases):

    . a) create new database in SSMS

    . b) generate the new database schema from ERwin, the tool I use to store the datamodel and which can be used to add all the tables, indexes, foreign keys etc.

    . c) run a script that copies all the data from the existing database into the new one with the new schema just created.

    We have a custom, home-grown application which creates the script for step c). But that custom application requires that we generate a file from ERwin that will determine the correct order for the tables to do the copying.

    I've always wondered how we could take that ERwin file out of the picture and figure out the correct table order using SQL. It is a much cleaner solution for a couple of reasons. For example, one time when ERwin released an "upgrade", the format of the report/file that we use got changed. So, we had to change the code in our custom app. It would be better to not have to rely on ERwin's table generating file.

    Thanks for the idea! I hope I will be able to use it in the future at some point.

  • Robert Davis

    One Orange Chip

    Points: 28027

    I ran the script on a database with three tables:

    1. dbo.LocationType (no foreign keys

    2. dbo.Locations (foreign key to dbo.LocationType)

    3. dbo.Fish (no foreign key defined, but it should have one to dbo.Locations)

    The script ouput was:

    Level A

    0 LocationType

    1 Locations

    Are tables with no references to or from excluded from the output intentionally? I would have expected dbo.Fish to be returned as a level 0 table.

    When I added the foreign key to dbo.Fish, I got the expected results of dbo.Fish being a level 2 table.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Glen Cooper

    SSCommitted

    Points: 1696

    Yes, tables involved in no relationships are ignored, for simplicity.

    Glen

    R Glen Cooper

  • Robert Davis

    One Orange Chip

    Points: 28027

    Thanks!! In that case, it seems to work perfectly. 🙂


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • adrlinux

    SSC Journeyman

    Points: 85

    Hello,

    Thank you very much for this awesome article !

    But would it be possible to have the same script for sql server 2000 ? Badly I am not able to do the transformation.

    Thank you very much.

  • Glen Cooper

    SSCommitted

    Points: 1696

    OK, I'll get back to you on this.

    Glen

    R Glen Cooper

  • Glen Cooper

    SSCommitted

    Points: 1696

    OK, this is an ugly kludge for SQL Server 2000.

    Replace the snippet for creating the preorder table with the one below.

    I can't think of a better way to do this.

    Glen

    -- Create a preorder table using the database

    -- relationships between tables. By definition,

    -- a table A will be 'less than' a table B if

    -- B has a foreign key pointing to A.

    -- SQL Server 2000 version.

    SELECT A, B

    INTO tblPreorder

    FROM

    (

    SELECT

    -- May be multiple relationships between two

    -- tables but we only need one

    DISTINCT

    -- Get name of parent table pointing to foreign table

    so2.name as B,

    -- Get name of foreign table by deleting

    -- name of parent table (along with punctuation)

    -- from name of foreign key constraint

    RIGHT(so1.name,LEN(so1.name) - (LEN(so2.name)+4)) as A

    FROM

    sysobjects so1

    INNER JOIN

    sysobjects so2

    ON

    so1.parent_obj = so2.id

    WHERE

    -- Restrict sysobjects to foreign key constraints

    so1.xtype='F'

    AND

    -- Make sure name of foreign table represents

    -- a real table because sysobjects will append

    -- 1, 2, etc. to foreign key constraint name if

    -- multiple relationships exist between a pair of them

    RIGHT(so1.name,LEN(so1.name) - (LEN(so2.name)+4)) IN (SELECT NAME FROM sysobjects WHERE xtype = 'U')

    ) derived

    ORDER BY A, B

    R Glen Cooper

  • adrlinux

    SSC Journeyman

    Points: 85

    Wonderful, thank you very much !!!!!

    I will use it immediatly.

    Best regards

  • Robert Davis

    One Orange Chip

    Points: 28027

    FYI: I referenced this article on my blog: http://www.sqlservercentral.com/blogs/robert_davis/archive/2009/03/29/Do-You-Support-the-Ancient-Ones.aspx


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • san_kan1gb

    Grasshopper

    Points: 24

    Very smart, elegant, beautiful solution/idea.

    Thank u for sharing ur brain power with us, thanks a lot.

    🙂 :w00t:

  • Glen Cooper

    SSCommitted

    Points: 1696

    Thank you for that.

    Those tables not involved in any relationships are not displayed (by design), but to display them simply add this snippet just before the script displays the final answer:

    -- Add to answer table those tables involved in no relationships (ie. what's left)

    -- Use -1 as Level

    INSERT INTO tblAnswer([Level], A)

    SELECT -1, name FROM dbo.sysobjects

    WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1

    AND name NOT IN ('sysdiagrams','tblPreorder','tblAnswer')

    AND name NOT IN

    (

    SELECT DISTINCT A FROM tblAnswer

    )

    R Glen Cooper

  • Ken Davis

    SSCarpal Tunnel

    Points: 4329

    Excellent script. I needed to drop most of the tables in database, in RI order. This is exactly what I need.

    Thanks!

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply