Using SQL DMO and Windows Script to customize script generationBy 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:
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-DMOThe 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 ScriptingWindows 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 OperandiLets 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 scriptThe 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 tablesNow 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 tablesThe Script method of the Table object takes the following syntax: TableObj.Script( [ ScriptType ] [, ScriptFilePath ] [, NewName ] [, Script2Type ] )
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 :
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
Download the Source CodeThe 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.
|
- Advertisement - |