SQL-DMO: Script all DB objects, all DBs

Script all database objects - including indexes and triggers - in all databases on a server. The long integer values control what it is that you are scripting. Values can be found here.

'--- setup objects
Set objSQL = CreateObject("SQLDMO.SQLServer")
Set fso = CreateObject ("Scripting.FileSystemObject")
Set WshShell = CreateObject("WScript.Shell")
'--- provide a root directory for folder and files
rootDir = "C:\Datadump\INSTANCE01\"
'---trusted login
objSQL.LoginSecure = True
' --- or non-trusted connection
' objSQL.LoginSecure = False
' objSQL.Login = "sa"
' objSQL.Password = "sa_pswd"
' ---
objSQL.Connect "SERVER01\INSTANCE01"

'---create the root folder if not already there
If Not(fso.FolderExists(rootDir)) Then
WshShell.Run "cmd /c md " & rootDir
End If

'---go get our data
Set objDB = objSQL.Databases

For each database in objDB
' Create subfolder for database
If NOT(fso.FolderExists(rootDir & database.name)) Then
fso.CreateFolder(rootDir & database.name)
End If

ScriptOut database.tables, "Tables"
ScriptOut database.StoredProcedures, "StoredProcs"
ScriptOut database.Views, "Views"
ScriptOut database.Users, "Users"
ScriptOut database.Rules, "Rules"
ScriptOut database.UserDefinedDatatypes, "UserDefDataType"
ScriptOut database.UserDefinedFunctions, "UserDefFunc"
Next

'---script SQLServerAgent jobs
Set objJob = objSQL.JobServer

If NOT(fso.FolderExists(rootDir & "\Jobs")) Then
fso.CreateFolder(rootDir & "\Jobs")
End If

For each job in objJob.Jobs
sFileName = rootDir & "\Jobs\" & Replace(job.Name, "\", "-") & ".sql"
job.Script 1203896487, sfileName
Next

'---routine to do the work
Sub ScriptOut(object, foldername)
on error resume next
For each item in object
If item.SystemObject = False Then
If NOT(fso.FolderExists(rootDir & database.Name & "\" & foldername)) Then
fso.CreateFolder(rootDir & database.Name & "\" & foldername )
End If

sFileName = rootDir & database.Name & "\" & foldername & "\" & Replace(item.Name, "\", "-") & ".sql"
item.Script 1204232383, sFileName
End If
Next
End Sub

'--- tidy up
Set objSQL = Nothing
Set fso = Nothing
Set objDB = Nothing