SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Oscar D Garcia / How to Get the Scripts for SQL Server Objects / Latest Posts

RE: How to Get the Scripts for SQL Server Objects

ozkary — Wed, 18 Nov 2009 08:01:42 GMT

hello, Take a look at the script (line 187). It uses the SQLDMOScript_Triggers property. This means that when the call to object.script (line 196) is made, the trigger will also be generated inside the TAB file for that table. I often used this option, and I am able to get the triggers that way. If you need to, the table object exposes a triggers collection which allows you to select each trigger object.I hope this helps

RE: How to Get the Scripts for SQL Server Objects

wooddsw — Wed, 18 Nov 2009 06:35:36 GMT

SQLDMO doesn't expose triggers that I can see(via Intellisense on oSQL). How could this script be modified to include the scripting of triggers?

RE: How to Get the Scripts for SQL Server Objects

Perry Whittle — Mon, 30 Mar 2009 14:46:42 GMT

great article. For free source control try Subversion, you can get it at[url]http://subversion.tigris.org/[/url]

RE: How to Get the Scripts for SQL Server Objects

ppcx — Thu, 26 Mar 2009 08:27:25 GMT

I'm interested in seeing how MS gets the data returned by particular views and functions/procedures. In 2000 I needed a view that would return user information from syslogins but without the password column. (the querying account did not have privileges to read the password column.) So I got the source for syslogins and created my own version minus the password column. In this case it worked because I could select from sysxlogins.

RE: How to Get the Scripts for SQL Server Objects

WayneS — Wed, 25 Mar 2009 20:52:45 GMT

[quote][b]ssaari (3/25/2009)[/b][hr]Thank you, Wayne,I looked at a couple of table scripts and didn't notice anything wrong. What kind of issues did you see if you don't mind?Scott[/quote]The thing I noticed is that the script only gets the first key (of Primary, Foreign or Unique keys)... primary if one is there. If the table has multiple (PK, 2+ FK, etc.) it only gets the PK. If the table doesn't have a PK, and has 2+ FKs, it only gets the first FK.Indexes, defaults and check constraints looked okay.

RE: How to Get the Scripts for SQL Server Objects

ssaari — Wed, 25 Mar 2009 19:01:43 GMT

Thank you, Wayne,I looked at a couple of table scripts and didn't notice anything wrong. What kind of issues did you see if you don't mind?Scott

RE: How to Get the Scripts for SQL Server Objects

WayneS — Wed, 25 Mar 2009 16:39:02 GMT

[quote][b]ssaari (3/25/2009)[/b][hr]Why didn't they bother to include a copy of a working version of the script? I have yet to see a script on this site that works as advertised out of the box.On Windows Server 2003, SQL2005, I had to put in a call to Main to get Main to run, replace StdOut with WScript.out to get it to list out the objects to the screen. However the subfolders do not get created and the files don't get written, even if I create the subfolders. The GetFolders function never gets called.Here's what I've got so far. Anybody want to take a crack at finishing it?[/quote]I took what you have, and refined it. This works. But in looking at the resultant scripts (especially for the tables), I see issues.[quote]'The usage for a SQL 2000/2005/2008 server with SQL Server authentication is:'cscript 0g_sqlextract.vbs localhost mydatabase c:\sqloutput sa mypassword'cscript scriptobjs.vbs localhost data_fusion c:\sql_obj_output df_runtime sheckels'This script connects to a SQL Server database using Windows or SQL Server authentication. Once it is connected, it iterates through the collection of objects in the database and scripts each one of them'The command line syntax to run the script is as follows:'cscript sqlextract.vbs [server] [database] [output folder] [username] [password]' * [server] server location/ip address/instance name' * [database] database name/initial catalog' * [output folder] a folder (must exists) where to write the files too' * [username] user (optional - sql authentication)' * [password] password (optional)'The usage for a SQL Express instance with Windows authentication is:'cscript 0g_sqlextract.vbs localhost\my-sql-instance mydatabase c:\sqloutputconst SQLDMODep_Children = 262144 'List all Microsoft® SQL Server components that depend on the referenced SQL Server component.const SQLDMOScript_IncludeHeaders = 131072 'Apply descending order to returned list.const SQLDMOScript_DRI_AllConstraints=520093696 'SQLDMOScript_DRI_Checks, SQLDMOScript_DRI_Defaults, SQLDMOScript_DRI_ForeignKeys, SQLDMOScript_DRI_PrimaryKey, and SQLDMOScript_DRI_UniqueKeys const SQLDMOScript_ToFileOnly = 64 'output fileconst SQLDMOScript_OwnerQualify = 262144 'object ownerconst SQLDMOScript_PrimaryObject = 4 'Generate Transact-SQL creating the referenced component.const SQLDMOScript_ObjectPermissions = 2 'Include Transact-SQL privilege defining statements when scripting database objects.const SQLDMOScript_IncludeIfNotExists =4096 'if existsconst SQLDMOScript_Indexes = 73736 'indexsconst SQLDMOScript2_NoCollation = 8388608 'no collationconst SQLDMOScript_Triggers = 16 'triggersconst SQLDMOScript_Drops = 1 'Generate Transact-SQL to remove the referenced component. Script tests for existence prior attempt to remove component.const TAB_FOLDER = "Tables"const VIW_FOLDER = "Views"const PROC_FOLDER = "Procedures"const UDF_FOLDER = "UDFs"call Main()'******************************************************************************sub Main'******************************************************************************'get the parameter list dim objArgs: Set objArgs = WScript.Argumentsif objArgs.Count > 2 then connString = objArgs(0) 'connection database = objArgs(1) 'database folder = objArgs(2) 'output folder if left(folder,1) <> "\" then folder = folder & "\" dim user dim pw Set oSql = WScript.CreateObject("SQLDMO.SQLServer2") if objArgs.Count > 4 then user = objArgs(3) pw = objArgs(4) Wscript.Echo "SQL Authentication - Connection to database" oSql.LoginSecure = False 'sql authentication oSql.Connect connString ,user,pw else Wscript.Echo "windows Authentication - Connection to database" oSql.LoginSecure = True 'windows authentication oSql.Connect connString end if call ScriptObjects(oSQL.Databases(database).Tables,"TAB","Reading tables: ", folder) call ScriptObjects(oSQL.Databases(database).Views,"VIW","Reading views: ", folder) call ScriptObjects(oSQL.Databases(database).StoredProcedures,"PRC","Reading procedures: ", folder) call ScriptObjects(oSQL.Databases(database).UserDefinedFunctions,"UDF","Reading functions: ", folder) oSql.Disconnect else Wscript.Echo "Usage: sqlextract.vbs [server or sql instance name] [database] [output folder]" end ifset oSql = nothing if err.Description <> "" then Wscript.Echo err.Descriptionend ifend sub'******************************************************************************sub ScriptObjects(list,ext,msg,folder)'******************************************************************************Dim objectWscript.Echo ""Wscript.Echo msg 'set the scripting optionsdim optionsoptions = SQLDMOScript_Indexes _ OR SQLDMOScript_Drops _ OR SQLDMOScript_IncludeIfNotExists _ OR SQLDMOScript_OwnerQualify _ OR SQLDMOScript_PrimaryObject _ Or SQLDMODep_Children _ Or SQLDMOScript_DRI_AllConstraints _ Or SQLDMODep_IncludeHeaders _ Or SQLDMOScript_ObjectPermissions _ Or SQLDMOScript_ToFileOnly _ OR SQLDMOScript2_NoCollation _ OR SQLDMOScript_TriggersFor Each object In list If Not object.SystemObject Then Wscript.Echo object.Name ' Something like this?? MyFolder = GetFolderName(ext, folder) ' What does the Script function do? object.Script options, MyFolder + "\" + object.Owner + "." + object.Name + "." + ext End IfNextend sub'******************************************************************************function GetFolderName(ext, folder)'******************************************************************************dim tmpFoldertmpFolder = ""select case ext case "TAB" tmpFolder = TAB_FOLDER case "VIW" tmpFolder = VIW_FOLDER case "PRC" tmpFolder = PROC_FOLDER case "UDF" tmpFolder = UDF_FOLDER end select'WScript.Echo folder + tmpFolder' I added the "call" and parenscall CreateFolder (folder + tmpFolder)GetFolderName = folder + tmpFolderend function'******************************************************************************sub CreateFolder(path) '******************************************************************************Dim fsoSet fso = CreateObject("Scripting.FileSystemObject")if not fso.FolderExists(path) then fso.CreateFolder(path)end ifset fso=nothingend sub[/quote]

RE: How to Get the Scripts for SQL Server Objects

ssaari — Wed, 25 Mar 2009 16:01:13 GMT

Why would you need to recreate system objects though?

RE: How to Get the Scripts for SQL Server Objects

ppcx — Wed, 25 Mar 2009 15:07:29 GMT

*Sigh* Except the scripted results may not be usable. For example, I scripted sys.server_principals and the resulting Create script shows it selecting from master.sys.sysxlgns which I can't find anywhere and you can't select from it.

RE: How to Get the Scripts for SQL Server Objects

ssaari — Wed, 25 Mar 2009 15:03:03 GMT

Why didn't they bother to include a copy of a working version of the script? I have yet to see a script on this site that works as advertised out of the box.On Windows Server 2003, SQL2005, I had to put in a call to Main to get Main to run, replace StdOut with WScript.out to get it to list out the objects to the screen. However the subfolders do not get created and the files don't get written, even if I create the subfolders. The GetFolders function never gets called.Here's what I've got so far. Anybody want to take a crack at finishing it?[quote]'The usage for a SQL 2000/2005/2008 server with SQL Server authentication is:'cscript 0g_sqlextract.vbs localhost mydatabase c:\sqloutput sa mypassword'cscript scriptobjs.vbs localhost data_fusion c:\sql_obj_output df_runtime sheckels'This script connects to a SQL Server database using Windows or SQL Server authentication. Once it is connected, it iterates through the collection of objects in the database and scripts each one of them'The command line syntax to run the script is as follows:'cscript sqlextract.vbs [server] [database] [output folder] [username] [password]' * [server] server location/ip address/instance name' * [database] database name/initial catalog' * [output folder] a folder (must exists) where to write the files too' * [username] user (optional - sql authentication)' * [password] password (optional)'The usage for a SQL Express instance with Windows authentication is:'cscript 0g_sqlextract.vbs localhost\my-sql-instance mydatabase c:\sqloutputconst SQLDMODep_Children = 262144 'List all Microsoft® SQL Server components that depend on the referenced SQL Server component.const SQLDMOScript_IncludeHeaders = 131072 'Apply descending order to returned list.const SQLDMOScript_DRI_AllConstraints=520093696 'SQLDMOScript_DRI_Checks, SQLDMOScript_DRI_Defaults, SQLDMOScript_DRI_ForeignKeys, SQLDMOScript_DRI_PrimaryKey, and SQLDMOScript_DRI_UniqueKeys const SQLDMOScript_ToFileOnly = 64 'output fileconst SQLDMOScript_OwnerQualify = 262144 'object ownerconst SQLDMOScript_PrimaryObject = 4 'Generate Transact-SQL creating the referenced component.const SQLDMOScript_ObjectPermissions = 2 'Include Transact-SQL privilege defining statements when scripting database objects.const SQLDMOScript_IncludeIfNotExists =4096 'if existsconst SQLDMOScript_Indexes = 73736 'indexsconst SQLDMOScript2_NoCollation = 8388608 'no collationconst SQLDMOScript_Triggers = 16 'triggersconst SQLDMOScript_Drops = 1 'Generate Transact-SQL to remove the referenced component. Script tests for existence prior attempt to remove component.call Main()sub Main 'get the parameter list dim objArgs: Set objArgs = WScript.Arguments if objArgs.Count > 2 then connString = objArgs(0) 'connection database = objArgs(1) 'database folder = objArgs(2) 'output folder dim user dim pw Set oSql = WScript.CreateObject("SQLDMO.SQLServer2") if objArgs.Count > 4 then user = objArgs(3) pw = objArgs(4) Wscript.Echo "SQL Authentication - Connection to database" oSql.LoginSecure = False 'sql authentication oSql.Connect connString ,user,pw else Wscript.Echo "windows Authentication - Connection to database" oSql.LoginSecure = True 'windows authentication oSql.Connect connString end if call ScriptObjects(oSQL.Databases(database).Tables,"TAB","Reading tables: ") call ScriptObjects(oSQL.Databases(database).Views,"VIW","Reading views: ") call ScriptObjects(oSQL.Databases(database).StoredProcedures,"PRC","Reading procedures: ") call ScriptObjects(oSQL.Databases(database).UserDefinedFunctions,"UDF","Reading functions: ") oSql.Disconnect else Wscript.Echo "Usage: sqlextract.vbs [server or sql instance name] [database] [output folder]" end if set oSql = nothing if err.Description <> "" then Wscript.Echo err.Description end ifend subsub ScriptObjects(list,ext,msg) Dim object Wscript.Echo "" Wscript.Echo msg 'set the scripting options dim options options = SQLDMOScript_Indexes _ OR SQLDMOScript_Drops _ OR SQLDMOScript_IncludeIfNotExists _ OR SQLDMOScript_OwnerQualify _ OR SQLDMOScript_PrimaryObject _ Or SQLDMODep_Children _ Or SQLDMOScript_DRI_AllConstraints _ Or SQLDMODep_IncludeHeaders _ Or SQLDMOScript_ObjectPermissions _ Or SQLDMOScript_ToFileOnly _ OR SQLDMOScript2_NoCollation _ OR SQLDMOScript_Triggers For Each object In list If Not object.SystemObject Then Wscript.Echo object.Name ' Something like this?? call GetFolderName(ext)' What does the Script function do? object.Script options, folder + "\" + object.Owner + "." + object.Name + "." + ext End If Nextend subfunction GetFolderName(ext) dim tmpFolder tmpFolder = "" select case ext case "TAB" tmpFolder = TAB_FOLDER case "VIW" tmpFolder = VIW_FOLDER case "PRC" tmpFolder = PROC_FOLDER case "UDF" tmpFolder = UDF_FOLDER end select' I added the "call" and parenscall CreateFolder (folder + tmpFolder)GetFolderName = folder + tmpFolderend functionsub CreateFolder(path) Dim fso Set fso = CreateObject("Scripting.FileSystemObject") if not fso.FolderExists(path) then fso.CreateFolder(path) end if set fso=nothingend sub[/quote]

RE: How to Get the Scripts for SQL Server Objects

ppcx — Wed, 25 Mar 2009 14:52:26 GMT

Well, I finally found out how to Script the system objects.[url=http://weblogs.sqlteam.com/mladenp/archive/2007/03/12/60132.aspx]http://weblogs.sqlteam.com/mladenp/archive/2007/03/12/60132.aspx[/url]Shut down your database, go to the directory with the database files, copy mssqlsystemresource.mdf and mssqlsystemresource.ldf to new names, like mssqlsystemresource1.mdf and mssqlsystemresource1.ldf. Start up the database and attach your new database. Then you can Script the system stuff.

RE: How to Get the Scripts for SQL Server Objects

ppcx — Wed, 25 Mar 2009 12:20:11 GMT

Will this script out system objects? In MSSQL 2000 I could "Create Object To New Window As Create" even on system objects (in the Master database). I can't figure out how to do that in MSSQL 2005. I need to see how some system views and functions work.

RE: How to Get the Scripts for SQL Server Objects

mmdmurphy — Wed, 25 Mar 2009 12:18:17 GMT

At the risk of sounding lazy, (it's more that I am neither a DBA nor a vb coder - I just do some becuase of my job) would it be possible to get a complete code? I am sort of at a loss as to how to combine this all into one "final" program...

RE: How to Get the Scripts for SQL Server Objects

roy-772084 — Wed, 25 Mar 2009 11:15:45 GMT

Would it be possible to include sample output for this? I'm particularly interested to see what you get for stored procedures. Is it possible to recover the SQL that was used to create the procedures if you have admin rights to the db?

RE: How to Get the Scripts for SQL Server Objects

ozkary — Wed, 25 Mar 2009 08:17:16 GMT

Hi, The idea behind this script is to use it for automation purposes. There are different ways in which it can be achieved. This is just one method.thanks for the feedback

RE: How to Get the Scripts for SQL Server Objects

brettveenstra — Wed, 25 Mar 2009 07:48:30 GMT

I use DbFriend for:- clean organization (I can put my files anywhere on disk, not just where SMSS says).- clean, repeatable scripts (SMSS script options are many and if I forget to check/uncheck an option, all my scripting time is wasted)- comparing against Production (again, clean scripting makes this a reliable DIFF)- DATABASE FIND & REPLACE- working in my environment of choice (VS 2008)- encourages me to THINK about my SQL assets as compiled code, that I have to RUN against a SQL box first, then exercise using SMSS (and with dual monitors, this is a very fluid workflow)

RE: How to Get the Scripts for SQL Server Objects

alen teplitsky — Wed, 25 Mar 2009 07:31:32 GMT

why use this instead of SSMS to script everything out?

RE: How to Get the Scripts for SQL Server Objects

brettveenstra — Wed, 25 Mar 2009 07:28:43 GMT

For scripting all your SQL objects to a Visual Studio Database project, you should do a Google search for "DbFriend".

RE: How to Get the Scripts for SQL Server Objects

brettveenstra — Wed, 25 Mar 2009 07:22:40 GMT

For scripting all your SQL objects to a Visual Studio Database project, you should do a Google search for "DbFriend".

RE: How to Get the Scripts for SQL Server Objects

brettveenstra — Wed, 25 Mar 2009 07:13:32 GMT

For scripting all your SQL objects to a Visual Studio Database project, you should do a Google search for "DbFriend".

RE: How to Get the Scripts for SQL Server Objects

Aaron C. Sentell — Wed, 25 Mar 2009 05:33:08 GMT

To be fair, I have not read your article, but I did notice that it uses DMO which is being replaced by SMO. We have found that although DMO does a fair job in SQL 2005, it does not script out everything as expected in SQL 2005 (e.g., jobs, varchar(max), etc.).Aaron

RE: How to Get the Scripts for SQL Server Objects

Aaron C. Sentell — Wed, 25 Mar 2009 05:29:21 GMT

RE: How to Get the Scripts for SQL Server Objects

Martin Selway-434765 — Wed, 25 Mar 2009 03:29:56 GMT

Why is the script called with cscript rather than wscript?From Technet:[font="Courier New"] [size="2"]Using the command-based script host (CScript.exe)Cscript.exe is a command-line version of the Windows Script Host that provides command-line options for setting script properties.With Cscript.exe, you can run scripts by typing the name of a script file at the command prompt. Like Microsoft Internet Explorer, Windows Script Host serves as a controller of Windows Script compliant scripting engines, but Windows Script Host has very low memory requirements. Windows Script Host is ideal for both interactive and non-interactive scripting needs, such as logon scripting and administrative scripting.Windows Script Host supports scripts written in VBScript or JScript. When you start a script from your desktop or from the command prompt, the script host reads and passes the specified script file contents to the registered script engine. The script engine uses file extensions (that is, .vbs for VBScript and .js for JScript) to identify the script. As a result, you do not need to know the exact programmatic identifier (that is, the ProgID) of the script engine. The script host maintains a mapping of script extensions to programmatic identifiers, and uses the Windows XP association model to start the appropriate engine for a given script. For more information about Windows Script Host, see Windows Script Host For more information about CScript.exe, see To run scripts using the command-line-based script host (cscript.exe) [/size][/font]

RE: How to Get the Scripts for SQL Server Objects

Mohit K. Gupta — Wed, 25 Mar 2009 01:16:16 GMT

Thanks for article, I had found something similar at CodePlex, [url=http://scriptdb.codeplex.com/]Link[/url].Only thing about CodePlex version was I couldn't get it to script out only the objects I wanted. So using DMO can give us that flexibility. Thanks.

How to Get the Scripts for SQL Server Objects

ozkary — Tue, 24 Mar 2009 23:20:01 GMT

Comments posted to this topic are about the item [B]How to Get the Scripts for SQL Server Objects[/B]