'on error resume next '******************************************************************************************************************************* ' * Objective: To Script the object listed in the objectlist.txt and the Dependant objects ' * Created by: MAK ' * Date: Feb 15, 2005 ' * Syntax: CSCRIPT GenDependScript.vbs Login Password objectlistFile Build# DestinationFolder ' * Usage Example: CSCRIPT GenDependScript.vbs Mylogin MyPwdcm c:\sqlserverdeploy\objectlist.txt 1588 c:\sqlserverdeploy\files\ '******************************************************************************************************************************* 'Check # of arguments Set objArgs = WScript.Arguments If objArgs.Count < 5 then wscript.echo "Syntax: CSCRIPT GenDependScript.vbs Login Password objectlistFile BUILD# DestinationFolder " & chr(10) & chr(10) & chr(10) & "Usage Example: CSCRIPT GenDependScript.vbs Mylogin MyPwdcm c:\sqlserverdeploy\objectlist.txt 1588 c:\sqlserverdeploy\files\ " wscript.quit end if ' Assign Arguments to variables Login=objArgs(0) password=objArgs(1) objlist=objArgs(2) CM=objArgs(3) TargetFolder =objArgs(4) 'ADODB connection Dim AdCn Dim AdRec Dim i, SQL Dim AdCn2 Dim AdRec2 Dim SQL2 Dim AdCn3 Dim AdRec3 Dim SQL3 Set AdCn = CreateObject("ADODB.Connection") Set AdRec = CreateObject("ADODB.Recordset") Set AdCn2 = CreateObject("ADODB.Connection") Set AdRec2 = CreateObject("ADODB.Recordset") Set AdCn3 = CreateObject("ADODB.Connection") Set AdRec3 = CreateObject("ADODB.Recordset") AdCn.CommandTimeout = 320 AdCn2.CommandTimeout = 320 'Assign current datetime as TimeStamp mydate=now() timestamp=year(mydate) & "_" & right("00"&month(mydate),2) & "_" & right("00"& day(mydate),2) & "_" & hour(mydate) & "_" & minute(mydate) & "_" & second(mydate) 'FSO log file Set FSO1 = CreateObject("Scripting.FileSystemObject") Set Stream1 = FSO1.CreateTextFile(Targetfolder & "\SQLServerDeploy_" & timestamp & "_Build_" & CM & ".log") Stream1.WriteLine "Generate SQL Script on objects and its Dependencies - Started >>" & now() Stream1.WriteLine "==============================================================" Stream1.WriteLine " " 'Create Primary Folder DirExists = fso1.folderexists(Targetfolder & timestamp & "_Build_" & CM) if DirExists = false then string1 = "Creating Folder " & Targetfolder & timestamp & "_Build_" & CM Stream1.WriteLine string1 Stream1.WriteLine " " fso1.CreateFolder (Targetfolder & timestamp & "_Build_" & CM) else string1 = "Folder already Exist" & Targetfolder & timestamp & "_Build_" & CM Stream1.WriteLine string1 end if DirExists ="" Set ifile = fso1.OpenTextFile(objlist) Do until ifile.AtEndOfLine 'Read Objectlist file primary=ifile.ReadLine '****************************************** 'primary="sql.sss.rr.dd" serverend=InStr(1,primary,".",1) Server=left(primary,serverend-1) Databaseend=InStr(serverend+1,primary,".",1) 'wscript.echo server Database=Mid(primary,serverend+1,(databaseend-serverend-1)) 'wscript.echo database primaryobjectname=right(primary,len(primary)-databaseend) 'wscript.echo primaryobjectname crossdbdepends1=right(primary,len(primary)-serverend) crossdbdepends2=Database & ".." & right(primaryobjectname,len(primaryobjectname)-InStr(1,primaryobjectname,".",1)) '****************************************** SQL="select user_name(uid)+'.'+object_name(id) as primaryObject, user_name(uid)+'.'+name as Objectname ,type from sysobjects where id=object_id('" & primaryobjectname & "') union select user_name(uid)+'.'+object_name(parent_obj) as primaryObject, user_name(uid)+'.'+name as Objectname ,type from sysobjects where type not in ('K','F','D','C') and parent_obj=object_id('" & primaryobjectname & "') union select user_name(uid)+'.'+object_name(parent_obj) as primaryobject ,user_name(uid)+'.'+object_name(id) as Objectname, TYPE from sysobjects where type not in ('K','F','D','C') and id in (select id from sysdepends where depid=object_id('" & primaryobjectname & "')) UNION select user_name(uid)+'.'+object_name(parent_obj) as primaryobject ,user_name(uid)+'.'+object_name(id) as Objectname, TYPE from sysobjects where type not in ('K','F','D','C') and id in (select depid from sysdepends where id=object_id('" & primaryobjectname & "')) UNION select user_name(OBJECTPROPERTY ( object_id('" & primaryobjectname & "'), 'ownerid') ) +'.'+object_name(id) as primaryObject, name as Objectname ,case indid when 1 then 'CLUS' else 'NON' end from sysindexes where name not like '%_WA_Sys_%' and indid not in (0,255) and id =object_id('" & primaryobjectname & "') and name not in (select name from sysobjects where parent_obj=object_id('" & primaryobjectname & "') and type ='K') " Stream1.WriteLine "Scripting Primary Dependencies for " & primary & " >> " & Now() Stream1.WriteLine "===============================================================================" Stream1.WriteLine " " Stream1.WriteLine "SQL Statement =" & SQL Stream1.WriteLine " " connstring = "Provider=SQLOLEDB.1;Data Source=" & Server & ";Initial Catalog=" & Database & ";user id = '" & Login & "';password='" & password & "' " 'wscript.echo connstring 'Stream1.WriteLine connstring AdCn.Open =connstring AdRec.Open SQL, AdCn,1,1 if adrec.recordcount = 0 then Stream1.WriteLine "Primary SQL returned zero rows. CLosing connections. CHecking for indexes" AdRec.close 'SQL="select user_name(OBJECTPROPERTY ( id, 'ownerid') ) +'.'+object_name(id) as primaryObject, name as Objectname ,case indid when 1 then 'CLUS' else 'NON' end as TYPE from sysindexes where name ='" & primaryobjectname & "' and indid<>0 and name not in (select name from sysobjects where type='K')" SQL="select user_name(OBJECTPROPERTY ( id, 'ownerid') ) +'.'+object_name(id) as primaryObject, name as Objectname ,case indid when 1 then 'CLUS' else 'NON' end as TYPE from sysindexes where name ='" & right(primaryobjectname,len(primaryobjectname)-InStr(1,primaryobjectname,".",1)) & "' and indid<>0 and name not in (select name from sysobjects where type='K')" Stream1.WriteLine "SQL Statement = " & SQL AdRec.Open SQL, AdCn,1,1 end if 'Create Folder 'DirExists = fso1.folderexists(Targetfolder & timestamp & "_Build_" & CM &"\" & primaryobjectname) DirExists = fso1.folderexists(Targetfolder & timestamp & "_Build_" & CM &"\" & primary) 'msgbox Targetfolder & timestamp & "_Build_" & CM &"\" & primary if DirExists = false then 'fso1.CreateFolder (Targetfolder & timestamp & "_Build_" & CM &"\" & primaryobjectname) fso1.CreateFolder (Targetfolder & timestamp & "_Build_" & CM &"\" & primary) Stream1.WriteLine "Creating Folder " & Targetfolder & timestamp & "_Build_" & CM &"\" & primaryobjectname else Stream1.WriteLine "Folder Already Exist " & Targetfolder & timestamp & "_Build_" & CM &"\" & primaryobjectname end if while not Adrec.EOF Objectnamepri=ltrim(rtrim(Adrec("primaryobject"))) Objecttype=ltrim(rtrim(Adrec("type"))) objectname=ltrim(rtrim(Adrec("objectname"))) 'filename = Targetfolder & timestamp & "_Build_" & CM &"\" & primaryobjectname & "\" & Objectnamepri & "_" & objectname& "." & Objecttype 'filename = Targetfolder & timestamp & "_Build_" & CM &"\" & primaryobjectname & "\" & objectname& "." & Objecttype 'filename = Targetfolder & timestamp & "_Build_" & CM &"\" & primary & "\" & objectname& "." & Objecttype filename = Targetfolder & timestamp & "_Build_" & CM &"\" & primary & "\" & Server & "." &Database & "." & objectname& "." & Objecttype Stream1.WriteLine "ScriptX " & Server & "," & login & "," & "********" & "," & Database & "," & Objectnamepri & ","& Objectname & "," & Objecttype & "," & filename ScriptX Server,login, password,Database,Objectnamepri,Objectname,Objecttype,filename objectname="" Adrec.movenext wend AdRec.close AdCn.close Stream1.WriteLine " " Stream1.WriteLine "Checking Cross database dependencies" Stream1.WriteLine "====================================" 'Cross Database dependencies connstring = "Provider=SQLOLEDB.1;Data Source=" & Server & ";Initial Catalog=master;user id = '" & Login & "';password='" & password & "' " 'wscript.echo connstring SQL2="select name from master.dbo.sysdatabases where name not in ('tempdb','pubs','northwind') and status & 32 != 32 and status & 64 != 64 and status & 128 != 128 and status & 256 != 256 and status & 512 != 512 and status & 1024 != 1024 and status & 4096 != 4096 and status & 32768 !=32768 and name <> '"& database & "' order by name" 'Stream1.WriteLine connstring Stream1.WriteLine " " Stream1.WriteLine "SQL Statement =" & SQL2 Stream1.WriteLine " " AdCn2.Open =connstring AdRec2.Open SQL2, AdCn2,1,1 while not Adrec2.EOF database2=adrec2("name") Stream1.WriteLine "Checking Cross database dependencies for " & database2 connstring2="Provider=SQLOLEDB.1;Data Source=" & Server & ";Initial Catalog=" & database2 & ";user id = '" & Login & "';password='" & password & "' " 'Stream1.WriteLine connstring2 AdCn3.open = connstring2 SQL="select user_name(uid)+'.'+object_name(a.id) as primaryObject, user_name(uid)+'.'+b.name as Objectname ,b.type from syscomments a join sysobjects b on a.id=b.id and (text like '% " & crossdbdepends1 & "%' or text like '% " & crossdbdepends2 & "%') and type <>'S'" Stream1.WriteLine " " Stream1.WriteLine "SQL Statement on " & database2 & "=" & SQL Stream1.WriteLine " " AdRec3.Open SQL, AdCn3,1,1 while not Adrec3.EOF ' Objectnamepri="" ' Objecttype="" ' objectname="" Objectnamepri=ltrim(rtrim(Adrec3("primaryobject"))) Objecttype=ltrim(rtrim(Adrec3("type"))) objectname=ltrim(rtrim(Adrec3("objectname"))) ' Stream1.WriteLine Objectnamepri filename = Targetfolder & timestamp & "_Build_" & CM &"\" & primary & "\" & Server & "." &Database2 & "." & objectname& "." & Objecttype Stream1.WriteLine "ScriptX " & Server & "," & login & "," & "********" & "," & Database2 & "," & Objectnamepri & ","& Objectname & "," & Objecttype & "," & filename Stream1.WriteLine "script Started " ScriptX Server,login, password,Database2,Objectnamepri,Objectname,Objecttype,filename Stream1.WriteLine "script completed " Adrec3.movenext wend AdRec3.close AdCn3.close Adrec2.movenext wend 'AdRec.close 'AdCn.close adrec2.close adcn2.close loop 'adrec2.close 'adcn2.close Stream1.WriteLine " " Stream1.WriteLine "Generate SQL Script on objects and its Dependencies - Completed >>" &Now() Stream1.WriteLine "===============================================================================" Stream1.WriteLine "Completed generating scripts for objects and its dependencies. Please check " & Targetfolder & "SQLServerDeploy_" & timestamp & "_Build_" & CM & ".log file for details" wscript.echo "Completed generating scripts for objects and its dependencies. Please check " & Targetfolder & "SQlServerdeploy_" & timestamp & "_Build_" & CM & ".log file for details" 'Function using SQLDMO to create Scripts Function ScriptX(Server,login, password,Database,Objectnamepri,Objectname,Objecttype,filename) 'SQLDMO Constants Const SQLDMOScript_Aliases = 16384 'Obsolete. Const SQLDMOScript_AppendToFile = 256 'Object Script method only. Append to indicated output file. By default, Script method overwrites existing file. Const SQLDMOScript_Bindings = 128 'Generate sp_bindefault and sp_bindrule statements. Applies only when scripting references a SQL Server table. Const SQLDMOScript_ClusteredIndexes = 8 'Generate Transact-SQL defining clustered indexes. Applies only when scripting references a SQL Server table. Const SQLDMOScript_DatabasePermissions = 32 'Generate Transact-SQL database privilege defining script. Database permissions grant or deny statement execution rights. Const SQLDMOScript_Default = 4 'Const SQLDMOScript_PrimaryObject. Const SQLDMOScript_DRI_All = 532676608 'All values defined as Const SQLDMOScript_DRI_... combined using an OR logical operator. Const SQLDMOScript_DRI_AllConstraints = 520093696 'Const SQLDMOScript_DRI_Checks, Const SQLDMOScript_DRI_Defaults, Const SQLDMOScript_DRI_ForeignKeys, Const SQLDMOScript_DRI_PrimaryKey, and Const SQLDMOScript_DRI_UniqueKeys combined using an OR logical operator. Const SQLDMOScript_DRI_AllKeys = 469762048 'Const SQLDMOScript_DRI_ForeignKeys, Const SQLDMOScript_DRI_PrimaryKey, Const SQLDMOScript_DRI_UniqueKeys combined using an OR logical operator. Const SQLDMOScript_DRI_Checks= 16777216 'Generated script creates column-specified CHECK constraints. Directs scripting when declarative referential integrity establishes dependency relationships. Applies only when scripting references a SQL Server table. Const SQLDMOScript_DRI_Clustered= 8388608 'Generated script creates clustered indexes. Directs scripting when declarative referential integrity establishes dependency relationships. Applies only when scripting references a SQL Server table. Const SQLDMOScript_DRI_Defaults = 33554432 'Generated script includes column-specified defaults. Directs scripting when declarative referential integrity establishes dependency relationships. Applies only when scripting references a SQL Server table. Const SQLDMOScript_DRI_ForeignKeys= 134217728 'Generated script creates FOREIGN KEY constraints. Directs scripting when declarative referential integrity establishes dependency relationships. Applies only when scripting references a SQL Server table. Const SQLDMOScript_DRI_NonClustered= 4194304 'Generated script creates nonclustered indexes. Directs scripting when declarative referential integrity establishes dependency relationships. Applies only when scripting references a SQL Server table. Const SQLDMOScript_DRI_PrimaryKey= 268435456 'Generated script creates PRIMARY KEY constraints. Directs scripting when declarative referential integrity establishes dependency relationships. Applies only when scripting references a SQL Server table. Const SQLDMOScript_DRI_UniqueKeys = 67108864 'Generated script creates candidate keys defined using a unique index. Directs scripting when declarative referential integrity establishes dependency relationships. Applies only when scripting references a SQL Server table. Const SQLDMOScript_DRIIndexes= 65536 'When Const SQLDMOScript_NoDRI is specified, script PRIMARY KEY constraints using a unique index to implement the declarative referential integrity. Applies only when scripting references a SQL Server table. Const SQLDMOScript_DRIWithNoCheck = 536870912 'When using Const SQLDMOScript_DRI_Checks, or Const SQLDMOScript_DRI_ForeignKeys, generated script includes the WITH NOCHECK clause optimizing constraint creation. Applies only when scripting references a SQL Server table. Const SQLDMOScript_Drops= 1 'Generate Transact-SQL to remove referenced component. Script tests for existence prior attempt to remove component. Const SQLDMOScript_IncludeHeaders = 131072 'Generated script is prefixed with a header containing date and time of generation and other descriptive information. Const SQLDMOScript_IncludeIfNotExists= 4096 'Transact-SQL creating a component is prefixed by a check for existence. When script is executed, component is created only when a copy of the named component does not exist. Const SQLDMOScript_Indexes= 73736 'Const SQLDMOScript_ClusteredIndexes, Const SQLDMOScript_NonClusteredIndexes, and Const SQLDMOScript_DRIIndexes combined using an OR logical operator. Const SQLDMOScript_NoCommandTerm = 32768 'Individual Transact-SQL statements in the script are not delimited using the connection-specific command terminator. By default, individual Transact-SQL statements are delimited. Const SQLDMOScript_NoDRI= 512 'Generated Transact-SQL statements do not include any clauses defining declarative referential integrity constraints. Applies only when scripting references a SQL Server table. Only use when script will execute on a version 4.21a SQL Server installation. Const SQLDMOScript_NoIdentity = 1073741824 'Generated Transact-SQL statements do not include definition of identity property, seed, and increment. Applies only when scripting references a SQL Server table. Const SQLDMOScript_NonClusteredIndexes= 8192 'Generate Transact-SQL defining nonclustered indexes. Applies only when scripting references a SQL Server table. Const SQLDMOScript_ObjectPermissions= 2 'Include Transact-SQL privilege defining statements when scripting database objects. Const SQLDMOScript_OwnerQualify = 262144 'Object names in Transact-SQL generated to remove an object are qualified by the owner of the referenced object. Transact-SQL generated to create the referenced object qualify the object name using the current object owner. Const SQLDMOScript_Permissions= 34 'Const SQLDMOScript_ObjectPermissions and Const SQLDMOScript_DatabasePermissions combined using an OR logical operator. Const SQLDMOScript_PrimaryObject= 4 'Generate Transact-SQL creating the referenced component. Const SQLDMOScript_SortedData = 1048576 'Obsolete. Const SQLDMOScript_SortedDataReorg = 2097152 'Obsolete. Const SQLDMOScript_TimestampToBinary= 524288 'When scripting object creation for a table or user-defined data type, convert specification of timestamp data type to binary(8). Const SQLDMOScript_ToFileOnly = 64 'Most SQL-DMO object scripting methods specify both a return value and an optional output file. When used, and an output file is specified, the method does not return the script to the caller, but only writes the script to the output file. Const SQLDMOScript_TransferDefault= 422143 'Default. Const SQLDMOScript_PrimaryObject, Const SQLDMOScript_Drops, Const SQLDMOScript_Bindings, Const SQLDMOScript_ClusteredIndexes, Const SQLDMOScript_NonClusteredIndexes, Const SQLDMOScript_Triggers, Const SQLDMOScript_ToFileOnly, Const SQLDMOScript_Permissions, Const SQLDMOScript_IncludeHeaders, Const SQLDMOScript_Aliases, Const SQLDMOScript_IncludeIfNotExists, and Const SQLDMOScript_OwnerQualify combined using an OR logical operator. Const SQLDMOScript_Triggers= 16 'Generate Transact-SQL defining triggers. Applies only when scripting references a SQL Server table. Const SQLDMOScript_UDDTsToBaseType= 1024 'Convert specification of user-defined data types to the appropriate SQL Server base data type. Applies only when scripting references a SQL Server table. Const SQLDMOScript_UseQuotedIdentifiers= -1 'Use quote characters to delimit identifier parts when scripting object names. Const SQLDMOScript2_AnsiFile= 2 'Create output file as a multibyte character text file. Code page 1252 is used to determine character meaning. Const SQLDMOScript2_AnsiPadding = 1 'Command batch includes the SET ANSI_PADDING ON and SET ANSI_PADDDING OFF Transact-SQL statements before and after CREATE TABLE statements in the generated script. Use when the article publishes a table. Const SQLDMOScript2_Default = 0 'Default. No scripting options specified. Const SQLDMOScript2_ExtendedProperty = 4194304 'Include extended property scripting as part of object scripting. Const SQLDMOScript2_FullTextIndex = 524288 'Command batch includes statements that define Microsoft Search full-text indexing. Use when the article publishes a table. Const SQLDMOScript2_NoCollation= 8388608 'Do not script the collation clause if source is later tha SQL Server version 7.0. The default is to generate collation. Const SQLDMOScript2_NoFG = 16 'Command batch does not include 'ON ' clause that directs filegroup use. Use when the article publishes a table. Const SQLDMOScript2_NoWhatIfIndexes= 512 'Command batch does not include CREATE STATISTICS statements. Use when the article publishes a table. Const SQLDMOScript2_UnicodeFile= 4 'Create output file as a Unicode character text file. 'SQL DMO COnnect to SQL Server set objDMO=createobject("SQLDMO.SQLServer") objDMO.Connect Server, Login, Password Set DB = objDMO.Databases(Database) 'Create File System Objects Set FSO = CreateObject("Scripting.FileSystemObject") Set Stream = FSO.CreateTextFile(Filename) Stream.WriteLine "--SQL Script on objects and its Dependencies" 'Create table Script if Objecttype ="U" then Set oTable = DB.Tables(objectname) Stream.WriteLine oTable.Script(SQLDMOScript_PrimaryObject Or SQLDMOScript_DRI_All or SQLDMOScript_Bindings or SQLDMOScript_Drops or SQLDMOScript_IncludeIfNotExists or SQLDMOScript_OwnerQualify ) end if 'Create View Script if Objecttype ="V" then Set oView = DB.views(objectname) Stream.WriteLine oView.Script(SQLDMOScript_PrimaryObject Or SQLDMOScript_DRI_All or SQLDMOScript_Bindings or SQLDMOScript_Drops or SQLDMOScript_IncludeIfNotExists or SQLDMOScript_OwnerQualify ) end if 'Create Rule Script if Objecttype ="R" then Set oRule = DB.Rules(objectname) Stream.WriteLine oRule.script(SQLDMOScript_PrimaryObject Or SQLDMOScript_DRI_All or SQLDMOScript_Drops or SQLDMOScript_IncludeIfNotExists or SQLDMOScript_OwnerQualify ) end if 'Create Default Script if Objecttype ="D" then Set oDefaults = DB.defaults(objectname) Stream.WriteLine odefaults.script(SQLDMOScript_PrimaryObject Or SQLDMOScript_DRI_All or SQLDMOScript_Drops or SQLDMOScript_IncludeIfNotExists or SQLDMOScript_OwnerQualify ) end if 'Create Procedure Script if Objecttype ="P" then Set oProc = DB.StoredProcedures(objectname) Stream.WriteLine oProc.Script(SQLDMOScript_PrimaryObject Or SQLDMOScript_ObjectPermissions or SQLDMOScript_Drops or SQLDMOScript_IncludeIfNotExists or SQLDMOScript_OwnerQualify ) end if 'Create Nonclustered index Script if Objecttype ="NON" then Set oTable = DB.Tables(Objectnamepri) Set oIndexes = oTable.Indexes(objectname) Stream.WriteLine oIndexes.Script(SQLDMOScript_PrimaryObject Or SQLDMOScript_DRI_NonClustered or SQLDMOScript_Drops or SQLDMOScript_IncludeIfNotExists or SQLDMOScript_OwnerQualify ) end if 'Create clustered index Script if Objecttype ="CLUS" then Set oTable = DB.Tables(Objectnamepri) Set oIndexes = oTable.Indexes(objectname) Stream.WriteLine oIndexes.Script(SQLDMOScript_PrimaryObject Or SQLDMOScript_DRI_Clustered or SQLDMOScript_Drops or SQLDMOScript_IncludeIfNotExists or SQLDMOScript_OwnerQualify ) end if 'Create Trigger Script if Objecttype ="TR" then Set oTable = DB.Tables(Objectnamepri) Set oTriggers= oTable.Triggers(objectname) Stream.WriteLine oTriggers.Script(SQLDMOScript_PrimaryObject Or SQLDMOScript_Triggers or SQLDMOScript_Drops or SQLDMOScript_IncludeIfNotExists or SQLDMOScript_OwnerQualify ) end if 'Create Function Script if Objecttype ="FN" or Objecttype ="IF" or Objecttype ="TF" then Set oFunction = DB.UserDefinedFunctions(objectname) Stream.WriteLine oFunction.Script(SQLDMOScript_PrimaryObject Or SQLDMOScript_ObjectPermissions or SQLDMOScript_Drops or SQLDMOScript_IncludeIfNotExists or SQLDMOScript_OwnerQualify ) end if objDMO.disconnect set objDMO= nothing set stream = nothing End function