InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 04:31:54 GMT20http://www.sqlservercentral.com/Forums/Topic680152-1514-1.aspxExcellent script. I needed to drop most of the tables in database, in RI order. This is exactly what I need.Thanks!Tue, 16 Nov 2010 07:10:37 GMTKen Davishttp://www.sqlservercentral.com/Forums/Topic680152-1514-1.aspxThank 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 LevelINSERT INTO tblAnswer([Level], A)SELECT -1, name FROM dbo.sysobjects WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1AND name NOT IN ('sysdiagrams','tblPreorder','tblAnswer')AND name NOT IN(SELECT DISTINCT A FROM tblAnswer)Tue, 09 Feb 2010 13:47:50 GMTGlen Cooperhttp://www.sqlservercentral.com/Forums/Topic680152-1514-1.aspxVery smart, elegant, beautiful solution/idea.Thank u for sharing ur brain power with us, thanks a lot.:-) :w00t:Tue, 09 Feb 2010 13:00:08 GMTsan_kan1gbhttp://www.sqlservercentral.com/Forums/Topic680152-1514-1.aspxFYI: I referenced this article on my blog: [url=http://www.sqlservercentral.com/blogs/robert_davis/archive/2009/03/29/Do-You-Support-the-Ancient-Ones.aspx]http://www.sqlservercentral.com/blogs/robert_davis/archive/2009/03/29/Do-You-Support-the-Ancient-Ones.aspx[/url]Sun, 29 Mar 2009 11:00:17 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic680152-1514-1.aspxWonderful, thank you very much !!!!!I will use it immediatly.Best regardsSun, 29 Mar 2009 09:32:25 GMTadrlinuxhttp://www.sqlservercentral.com/Forums/Topic680152-1514-1.aspxOK, 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, BINTO tblPreorderFROM(SELECT-- May be multiple relationships between two-- tables but we only need oneDISTINCT -- Get name of parent table pointing to foreign tableso2.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 so1INNER JOINsysobjects so2ONso1.parent_obj = so2.idWHERE-- 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')) derivedORDER BY A, BMon, 23 Mar 2009 15:39:22 GMTGlen Cooperhttp://www.sqlservercentral.com/Forums/Topic680152-1514-1.aspxOK, I'll get back to you on this.GlenMon, 23 Mar 2009 13:50:14 GMTGlen Cooperhttp://www.sqlservercentral.com/Forums/Topic680152-1514-1.aspxHello, 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.Mon, 23 Mar 2009 12:29:55 GMTadrlinuxhttp://www.sqlservercentral.com/Forums/Topic680152-1514-1.aspxThanks!! In that case, it seems to work perfectly. :)Fri, 20 Mar 2009 12:50:13 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic680152-1514-1.aspxYes, tables involved in no relationships are ignored, for simplicity.GlenFri, 20 Mar 2009 12:16:27 GMTGlen Cooperhttp://www.sqlservercentral.com/Forums/Topic680152-1514-1.aspxI ran the script on a database with three tables:1. dbo.LocationType (no foreign keys2. 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 A0 LocationType1 LocationsAre 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.Fri, 20 Mar 2009 10:50:46 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic680152-1514-1.aspxAwesome 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.Fri, 20 Mar 2009 10:28:08 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic680152-1514-1.aspxCould someone fix the table? Its got &lt;tr&gt's instead of &lt;td&gt'sFri, 20 Mar 2009 02:29:16 GMTJohn Muirhttp://www.sqlservercentral.com/Forums/Topic680152-1514-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Database+Design/66071/">Ordering Tables To Preserve Referential Integrity</A>[/B]Fri, 20 Mar 2009 00:22:32 GMTGlen Cooper