Automatic DBA

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

Begin

Insert Into #TableOrder

( TableName

, TableLevel

)

Select SO.name

, @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

INNER JOIN SYS.FOREIGN_KEYS SF

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 SOR.name not in ( Select TableName

From #TableOrder

)

)

and SO.name not in ( Select TableName

From #TableOrder

)

select @Continue = @@rowcount

, @TableLevel = @TableLevel + 1

print @Continue

End

select *

from #TableOrder

order by

TableLevel desc

drop table #TableOrder