Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Ordering Tables To Preserve Referential Integrity Expand / Collapse
Author
Message
Posted Friday, March 20, 2009 12:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 3:20 PM
Points: 51, Visits: 255
Comments posted to this topic are about the item Ordering Tables To Preserve Referential Integrity

R Glen Cooper
glencooper.tel
Post #680152
Posted Friday, March 20, 2009 2:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 17, 2011 4:53 AM
Points: 4, Visits: 51
Could someone fix the table? Its got <tr>'s instead of <td>'s
Post #680195
Posted Friday, March 20, 2009 10:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 04, 2014 5:19 PM
Points: 265, Visits: 2,538
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.
Post #680548
Posted Friday, March 20, 2009 10:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:29 AM
Points: 1,592, Visits: 1,488
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #680570
Posted Friday, March 20, 2009 12:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 3:20 PM
Points: 51, Visits: 255
Yes, tables involved in no relationships are ignored, for simplicity.

Glen


R Glen Cooper
glencooper.tel
Post #680631
Posted Friday, March 20, 2009 12:50 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:29 AM
Points: 1,592, Visits: 1,488
Thanks!! In that case, it seems to work perfectly. :)



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #680643
Posted Monday, March 23, 2009 12:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 13, 2010 10:37 AM
Points: 5, 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.

Post #681733
Posted Monday, March 23, 2009 1:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 3:20 PM
Points: 51, Visits: 255
OK, I'll get back to you on this.

Glen


R Glen Cooper
glencooper.tel
Post #681807
Posted Monday, March 23, 2009 3:39 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 3:20 PM
Points: 51, Visits: 255
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
glencooper.tel
Post #681921
Posted Sunday, March 29, 2009 9:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 13, 2010 10:37 AM
Points: 5, Visits: 50
Wonderful, thank you very much !!!!!
I will use it immediatly.

Best regards




Post #685728
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse