March 20, 2009 at 12:22 am
Comments posted to this topic are about the item Ordering Tables To Preserve Referential Integrity
March 20, 2009 at 2:29 am
Could someone fix the table? Its got <tr>'s instead of <td>'s
March 20, 2009 at 10:28 am
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.
March 20, 2009 at 10:50 am
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:
LevelA
0LocationType
1Locations
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.
March 20, 2009 at 12:16 pm
Yes, tables involved in no relationships are ignored, for simplicity.
Glen
March 20, 2009 at 12:50 pm
Thanks!! In that case, it seems to work perfectly. 🙂
March 23, 2009 at 12:29 pm
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.
March 23, 2009 at 1:50 pm
OK, I'll get back to you on this.
Glen
March 23, 2009 at 3:39 pm
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
March 29, 2009 at 9:32 am
Wonderful, thank you very much !!!!!
I will use it immediatly.
Best regards
March 29, 2009 at 11:00 am
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
February 9, 2010 at 1:00 pm
Very smart, elegant, beautiful solution/idea.
Thank u for sharing ur brain power with us, thanks a lot.
🙂 :w00t:
February 9, 2010 at 1:47 pm
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
)
November 16, 2010 at 7:10 am
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 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply