Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Get the Scripts for SQL Server Objects


How to Get the Scripts for SQL Server Objects

Author
Message
ozkary
ozkary
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 141
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

http://ozkary.blogspot.com
http://og-bit.com
roy-772084
roy-772084
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 12
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?
mmdmurphy
mmdmurphy
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 30
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...
ppcx
ppcx
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 431
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.
ppcx
ppcx
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 431
Well, I finally found out how to Script the system objects.

http://weblogs.sqlteam.com/mladenp/archive/2007/03/12/60132.aspx

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.
ssaari
ssaari
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 61
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
ppcx
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 431
*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
ssaari
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 61
Why would you need to recreate system objects though?
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6230 Visits: 10398
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

ssaari
ssaari
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 61
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search