Using SQL DMO and Windows Script to customize script generation

By Guest Authors on 23 September 2002 | Tags: DMO

Creating and modifying table scripts can quickly turn into a tedious chore. The article illustrates a method to automate this task by using SQL-DMO and Windows Script to generate customized scripts for SQL server table objects. This article was written by Ashok.

One of my recent projects was to implement an audit trail for an existing application running on SQL Server. I decided to use triggers to implement the audit trail and created an audit database containing the audit trail tables. (Something similar to what is described in Using triggers to track database action history)

The structure of the audit trail tables duplicated the structure of the application table except that:

• All constraints were removed
• All not null fields were made null able
• All identity fields were made into their respective data-type fields.

For e.g. If the application table was structured like this:

CREATE TABLE [dbo].[ EMPLOYEE] (
	[EMP_CODE] numeric](18, 0) IDENTITY (1, 1) NOT NULL,
	[EMP_NAME] [varchar] (50) NOT NULL ,
	[DB_FLAG] [char] (1) NOT NULL ,
	[CUSERID] [varchar] (10) NOT NULL ,
	[CDATE] [datetime] NOT NULL ,
	[MUSERID] [varchar] (10) NULL ,
	[MDATE] [datetime] NULL 
) ON [PRIMARY]
GO

The audit table (that resides in a different database) would be structured like this :

/*new table name prefixed with AUDIT_*/
CREATE TABLE [dbo].[ AUDIT_EMPLOYEE] (
	/*new columns*/
	[AUDIT_ID] int identity(1,1) not null,
	[AUDIT_MODIFIER] varchar(10) null,
	[AUDIT_HOSTNAME] varchar(255) null,
	[AUDIT_DATETIME] datetime  null,
	[AUDIT_ACTION] varchar (10) null,
	/*end of new columns*/	
	/*All the other fields identical to the main table */
	/*identity property has been stripped */
	/*all not null properties have been converted to NULL*/	
	[EMP_CODE] numeric](18, 0) NULL ,
	[EMP_NAME] [varchar] (50) NULL ,
	[DB_FLAG] [char] (1) NULL ,,
	[CUSERID] [varchar] (10) NULL ,
	[CDATE] [datetime] NULL ,
	[MUSERID] [varchar] (10) NULL ,
	[MDATE] [datetime] NULL 
) ON [PRIMARY]
GO

As you can see the audit table duplicates the fields of the application table and has a few more additional fields. The audit trail had to cover around 60 tables and time was at a premium. I started by using the "Generate SQL Script" option in SQL Enterprise Manager on the specific tables and manually modidfying each generated script into the audit table script. Doing this for a lot of tables can turn out to be very tedious & time consuming! But, could there be an easier way?

Enter SQL-DMO

The SQL Distributed Management Objects (SQL-DMO) are a collection of COM interfaces that allow the management of SQL Server. A good introduction to SQL-DMO is this article by Merkin (An Introduction to SQL-DMO). SQL-DMO also allows scripting of individual database objects via a Script() method.

Enter Windows Scripting

Windows Scripting is meant to be a replacement for the somewhat limited windows batch file language. Windows 2000 installations ship with the scripting engine and command-line interpreter for running windows scripts – the default scripting installation supports scripts written in VBScript and Jscript (a variant of JavaScript). For details on using windows scripting see: msdn.microsoft.com/scripting/ and www.win32scripting.com. The scripts used in this article make use of JScript.

The Modus Operandi

Lets see how we use Windows Scripting to generate the audit table scripts:

The audit tables have some extra fields that are used for the audit, these are in addition to the fields they inherit from the application tables. The extra fields are identical across all the tables. Create a text file called audit_new_field.txt with the following code in it:

	[AUDIT_ID] int identity(1,1) not null,
	[AUDIT_MODIFIER] varchar(10) null,
	[AUDIT_HOSTNAME] varchar(255)null,
	[AUDIT_DATETIME] datetime  null,
	[AUDIT_ACTION] varchar (10) null,

As you can see it contains just the TSQL definitions for the new fields that appear in all the audit tables.

Finally: Save this file in a sub-directory called ‘settings’. The windows script appends these fields into every audit table script that it generates. The windows script picks up the field definitions from the audit_new_field.txt file. Now we are all set to dive into the code generator script. I pass in the connection information as parameters to the table builder script:

C:\>cscript codegen.wsf //job:tablebuild server-name db_name username password

(cscript.exe is the command line interpreter for windows scripting. codegen.wsf is the windows script file. tablebuild is the task name that I want to execute within the script file. Type cscript/? to see detailed cscript usage.)

The Table Builder script

The table builder script connects to a SQL Server, using the SQLDMO.Server object’s Connect() function. The script makes use of the connection information passed as parameters from the command line to make the connection. The Connect() function returns a SQLServer object.

   sqServer = new ActiveXObject("SQLDMO.SQLServer");
   sqServer.Connect(server0, user3, pass4);
   //pass in server name, user name & password. (I use SQL authentication)

The SQLServer object has a ‘Databases’ collection; we get an object handle to the desired database from this collection.

   sqDb = new ActiveXObject("SQLDMO.Database");
   sqDb = sqServer.Databases(db1);

Iterating through the tables

Now we enumerate through the Tables collection (found within a Database object) and Script() only the User created tables, by checking the Table object’s SystemObject property.

   tableColl = new Enumerator(sqDb.Tables);
   for (; !tableColl.atEnd(); tableColl.moveNext())
   	{
   	   var strTableScript;
	   sqTable = tableColl.item();
	   if (!sqTable.SystemObject)
	   	{
		--table.script() code after this--

Scripting the tables

The Script method of the Table object takes the following syntax:

TableObj.Script( [ ScriptType ] [, ScriptFilePath ] [, NewName ] [, Script2Type ] )

• ScriptType – this field can be used to override the default behavior of the Script() function
• ScriptFilePath – the script can be output to a file on disk, specified in this parameter.
• NewName – This parameter specifies a new name for the referenced table object.
• Script2Type – this parameter is used to specify SQL Server 2000 specific parameters.

The script calls the Script() function in the following manner :

var lngParams = iSQLDMOScript_Drops | iSQLDMOScript_IncludeIfNotExists| 
    iSQLDMOScript_IncludeHeaders | iSQLDMOScript_Default |     
    iSQLDMOScript_NoIdentity|iSQLDMOScript_NoDRI;
sqTable.Script(lngParams , "", "AUDIT_"+sqTable.Name, iSQLDMOScript2_NoCollation);

Lets examine the parameters one by one:

Param #1 : The lngParams parameters customizes the generated script, by combining various attribute constants :

• iSQLDMOScript_Drops: I want the script to have a ‘if exists() drop table…’ before every create table.
• iSQLDMOScript_IncludeIfNotExists : I want to include an existence check on the object.
• iSQLDMOScript_IncludeHeaders : I want to include header comments containing name of object & time of creation.
• iSQLDMOScript_Default : this parameter is required to generate the ‘create table ‘ script
• iSQLDMOScript_NoIdentity : I want to disable generation of definitions for the identity property, seed or increment .
• iSQLDMOScript_NoDRI : I want to disable generation of TSQL script statements to create DRI constraints.

Param #2: I leave the 2nd parameter blank because I don’t want to output the generated script to a file immediately.

Param #3: I generate a new name for the audit table; I simply append AUDIT_ before the existing application table name. For instance: if the application table name were Employee, the audit table name would be AUDIT_Employee.

Param #4: I pass iSQLDMOScript2_NoCollation as the last parameter. This option disables scripting of the collation clause in SQL 2000 scripts.

The Script() method returns the generated script in a string variable. At this stage though, the script is not yet ready for use; the generated script could still have fields with not null criteria (remember that in our audit table all fields are null able). A javascript Search & replace function is applied on the string to convert all instances of not null, to null.

strTableScript = replace(strTableScript,"NOT NULL", "NULL");

We still need to append the TSQL statements for the extra fields listed in the audit_new_field.txt (described in the Modus Operandi section) to the generated script. How do we do this? We can do this quite easily if we append the script right at the beginning of the table generation script. I can find the starting position for the new fields by doing a search for the endpoint of the string:

“CREATE TABLE [AUDIT_table_name] (“

And insert the text from the audit_new_field.txt file at that point. (Read the inline comments in the code shown below)

//…continuing from earlier code….
// dynamically build the string to search for
strLineToSearchFor = "CREATE TABLE [AUDIT_"+sqTable.Name+"] (";	   	
var i;
// search for insertion point within the strTableScript variable 
// that contains the generated script
i = strTableScript.indexOf(strLineToSearchFor);
 	if (i != -1) //insertion point found
 {
	var tmp='';
	var tsOut;
	tmp = strTableScript.substring(0,i+strLineToSearchFor.length);
	tmp = tmp+"\n"+strNewFields; //add the new fields at the correct offset
	tmp=tmp+strTableScript.substring(i+strLineToSearchFor.length+1, strTableScript.length)
	tsOut =	fso.CreateTextFile("scripts\\"+sqTable.Name+".txt", true);  
        // output the transformed generated script to 
        // a sub-directory called ‘scripts’
	tsOut.Write(tmp);
	tsOut.Close();
   }
else
WScript.Echo(strLineToSearchFor+" : NOT FOUND");
      }
}
sqServer.Close(); //close the connection

The Benefits of using this method

• This method can be used to generate customized scripts for a large number of tables in a short time.
• Transformation of scripts can be automated to an extent, thus reducing possibility of human error.
• Adding new fields to the audit tables is very easy now: add the new field definition to the audit_new_field.txt file and re-rerun the generator script.
• The use of a parameter driven script makes it very portable across different SQL-Server installations.

Download the Source Code

The windows script file (WSF file) mentioned in this article can be downloaded from here. You will need the SQL-DMO libraries installed on your PC for the code to work.