Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Ordering Tables To Preserve Referential Integrity


Ordering Tables To Preserve Referential Integrity

Author
Message
Glen Cooper
Glen Cooper
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 287
Comments posted to this topic are about the item Ordering Tables To Preserve Referential Integrity

R Glen Cooper
John Muir
John Muir
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 51
Could someone fix the table? Its got <tr>'s instead of <td>'s
JJ B
JJ B
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 2850
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
Robert Davis
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1970 Visits: 1623
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
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
Glen Cooper
Glen Cooper
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 287
Yes, tables involved in no relationships are ignored, for simplicity.

Glen

R Glen Cooper
Robert Davis
Robert Davis
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1970 Visits: 1623
Thanks!! In that case, it seems to work perfectly. Smile



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
adrlinux
adrlinux
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 50
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
Glen Cooper
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 287
OK, I'll get back to you on this.

Glen

R Glen Cooper
Glen Cooper
Glen Cooper
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 287
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
adrlinux
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 50
Wonderful, thank you very much !!!!!
I will use it immediatly.

Best regards
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search