Posts Tagged ‘SQL Server’

“I’ve got an order for table 4!”

Thursday, June 7th, 2012

One of the most useful things anyone dealing with a database can learn is the system tables. Period. And there are many reason for this, most of which revolve around getting tedious work done fast and correctly.

Ever wanted to automate table DDL scripts so that they run in the correct order? If you know how the system tables work, you could just ask the database.

Case in point: I automated SQL builds for TEST, QA, Proof, and Prod. One of the issues was that I needed to know what order the table scripts needed to be run in so that I didn’t get a foreign key construction error. My solution was to query the DEV box that the developers used to create/modify the scripts. The steps top make this work:

  1. Get the scripts that were created/modified since the start of the release.
  2. Get the order of all tables from DEV database based on the Parent-Child relationships (foreign keys).
  3. Order the scripts based on where they fell in the list and execute in that order.

Easy! And I can use it to automatically execute the scripts (as I did in TEST), or I can use it to create the properly order DDL script for a TCW request (as I did for QA and PROD). The script is below:

 Declare @Continue int
, @ColContinue int
, @InterSQL nvarchar(4000)
, @SQL nvarchar(4000)
, @TableName varchar(250)
, @ColumnName varchar(250)
, @AllColumns varchar(4000)
, @DatabaseName varchar(200)
, @SourceDatabase varchar(200)
, @TableLevel int
Select @SourceDatabase = DB_Name()
Create table #TableOrder
( [Order] [int] IDENTITY (1, 1) NOT NULL
, [TableName] [varchar] (250) NOT NULL
, [TableLevel] [int] DEFAULT (0)
select @Continue = 1
, @TableLevel = 0
while @Continue > 0
Insert Into #TableOrder
( TableName
, TableLevel
, @TableLevel
from sys.objects SO
where type = 'U'
and SO.object_ID not in ( select distinct so.object_id
from sys.foreign_key_columns SFK
on SFK.Constraint_object_ID = SF.object_ID
inner join sys.objects SOC
on SFK.Constraint_Object_ID = SOC.Object_ID
inner join sys.objects SO
on SFK.referenced_Object_ID = SO.Object_ID
inner join sys.objects SOR
on SFK.Parent_Object_ID = SOR.Object_ID
inner join sys.columns SC
on SOR.Object_ID = SC.Object_ID
and SFK.Parent_Column_ID = Sc.Column_ID
inner join sys.columns SCP
on SO.Object_ID = SCP.Object_ID
and SFK.Referenced_Column_ID = SCP.Column_ID
inner join sys.schemas S
on SOC.Schema_ID = S.Schema_ID
where not in ( Select TableName
From #TableOrder
and not in ( Select TableName
From #TableOrder
select @Continue = @@rowcount
, @TableLevel = @TableLevel + 1
print @Continue
select *
from #TableOrder
order by
TableLevel desc
drop table #TableOrder