How to Get the Scripts for SQL Server Objects

  • ssaari

    SSC Journeyman

    Points: 92

    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?

    '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:\sqloutput

    const 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 file

    const SQLDMOScript_OwnerQualify = 262144 'object owner

    const 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 exists

    const SQLDMOScript_Indexes = 73736 'indexs

    const SQLDMOScript2_NoCollation = 8388608 'no collation

    const SQLDMOScript_Triggers = 16 'triggers

    const 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 if

    end sub

    sub 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

    Next

    end sub

    function 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 parens

    call CreateFolder (folder + tmpFolder)

    GetFolderName = folder + tmpFolder

    end function

    sub CreateFolder(path)

    Dim fso

    Set fso = CreateObject("Scripting.FileSystemObject")

    if not fso.FolderExists(path) then

    fso.CreateFolder(path)

    end if

    set fso=nothing

    end sub

  • ppcx

    Ten Centuries

    Points: 1359

    *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.

  • ssaari

    SSC Journeyman

    Points: 92

    Why would you need to recreate system objects though?

  • WayneS

    SSC Guru

    Points: 95392

    ssaari (3/25/2009)


    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?

    I took what you have, and refined it. This works. But in looking at the resultant scripts (especially for the tables), I see issues.

    '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:\sqloutput

    const 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 file

    const SQLDMOScript_OwnerQualify = 262144 'object owner

    const 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 exists

    const SQLDMOScript_Indexes = 73736 'indexs

    const SQLDMOScript2_NoCollation = 8388608 'no collation

    const SQLDMOScript_Triggers = 16 'triggers

    const 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.Arguments

    if 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 if

    set oSql = nothing

    if err.Description <> "" then

    Wscript.Echo err.Description

    end if

    end sub

    '******************************************************************************

    sub ScriptObjects(list,ext,msg,folder)

    '******************************************************************************

    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??

    MyFolder = GetFolderName(ext, folder)

    ' What does the Script function do?

    object.Script options, MyFolder + "\" + object.Owner + "." + object.Name + "." + ext

    End If

    Next

    end sub

    '******************************************************************************

    function GetFolderName(ext, folder)

    '******************************************************************************

    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

    'WScript.Echo folder + tmpFolder

    ' I added the "call" and parens

    call CreateFolder (folder + tmpFolder)

    GetFolderName = folder + tmpFolder

    end function

    '******************************************************************************

    sub CreateFolder(path)

    '******************************************************************************

    Dim fso

    Set fso = CreateObject("Scripting.FileSystemObject")

    if not fso.FolderExists(path) then

    fso.CreateFolder(path)

    end if

    set fso=nothing

    end sub

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • ssaari

    SSC Journeyman

    Points: 92

    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

  • WayneS

    SSC Guru

    Points: 95392

    ssaari (3/25/2009)


    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

    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.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • ppcx

    Ten Centuries

    Points: 1359

    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.

  • Perry Whittle

    SSC Guru

    Points: 233779

    great article. For free source control try Subversion, you can get it at

    http://subversion.tigris.org/

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • wooddsw

    SSC Enthusiast

    Points: 144

    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?

  • Oscar D. Garcia

    SSC Eights!

    Points: 864

    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

    https://ozkary.com

Viewing 10 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply