Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Generates scripts for most SQL Server database objects using PowerShell (SMO objects)
################################################################################################################################
#
# Script Name : SmoDb
# Version : 1.0
# Author : Vince Panuccio
# Purpose :
# This script generates one SQL script per database object including Stored Procedures,Tables,Views,
# User Defined Functions and User Defined Table Types. Useful for versionining a databsae in a CVS.
#
# Usage :
# Set variables at the top of the script then execute.
#
# Note :
# Only tested on SQL Server 2008r2
#
################################################################################################################################
$server = "localhost"
$database = "NerdDinner"
$output_path = "C:\dev\nerddinner\Schema"
$schema = "dbo"
$table_path = "$output_path\Table\"
$storedProcs_path = "$output_path\StoredProcedure\"
$triggers_path = "$output_path\Triggers\"
$views_path = "$output_path\View\"
$udfs_path = "$output_path\UserDefinedFunction\"
$textCatalog_path = "$output_path\FullTextCatalog\"
$udtts_path = "$output_path\UserDefinedTableTypes\"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database")
$tbl = New-Object ("Microsoft.SqlServer.Management.SMO.Table")
$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server)
# Get the database and table objects
$db = $srv.Databases[$database]
$tbl = $db.tables | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject }
$storedProcs = $db.StoredProcedures | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject }
$triggers = $db.Triggers + ($tbl | % { $_.Triggers })
$views = $db.Views | Where-object { $_.schema -eq $schema }
$udfs = $db.UserDefinedFunctions | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject }
$catlog = $db.FullTextCatalogs
$udtts = $db.UserDefinedTableTypes | Where-object { $_.schema -eq $schema }
# Set scripter options to ensure only data is scripted
$scripter.Options.ScriptSchema = $true;
$scripter.Options.ScriptData = $false;
#Exclude GOs after every line
$scripter.Options.NoCommandTerminator = $false;
$scripter.Options.ToFileOnly = $true
$scripter.Options.AllowSystemObjects = $false
$scripter.Options.Permissions = $true
$scripter.Options.DriAllConstraints = $true
$scripter.Options.SchemaQualify = $true
$scripter.Options.AnsiFile = $true
$scripter.Options.SchemaQualifyForeignKeysReferences = $true
$scripter.Options.Indexes = $true
$scripter.Options.DriIndexes = $true
$scripter.Options.DriClustered = $true
$scripter.Options.DriNonClustered = $true
$scripter.Options.NonClusteredIndexes = $true
$scripter.Options.ClusteredIndexes = $true
$scripter.Options.FullTextIndexes = $true
$scripter.Options.EnforceScriptingOptions = $true
function CopyObjectsToFiles($objects, $outDir) {
if (-not (Test-Path $outDir)) {
[System.IO.Directory]::CreateDirectory($outDir)
}
foreach ($o in $objects) {
if ($o -ne $null) {
$schemaPrefix = ""
if ($o.Schema -ne $null -and $o.Schema -ne "") {
$schemaPrefix = $o.Schema + "."
}
$scripter.Options.FileName = $outDir + $schemaPrefix + $o.Name + ".sql"
Write-Host "Writing " $scripter.Options.FileName
$scripter.EnumScript($o)
}
}
}
# Output the scripts
CopyObjectsToFiles $tbl $table_path
CopyObjectsToFiles $storedProcs $storedProcs_path
CopyObjectsToFiles $triggers $triggers_path
CopyObjectsToFiles $views $views_path
CopyObjectsToFiles $catlog $textCatalog_path
CopyObjectsToFiles $udtts $udtts_path
CopyObjectsToFiles $udfs $udfs_path
Write-Host "Finished at" (Get-Date)
@tommylux

This comment has been minimized.

Copy link

@tommylux tommylux commented Apr 24, 2017

The Tabs used on lines 40-46 are automatically auto-completing the variable to the "$storedProcs_Path" etc.. This shouldn't happen when running the code from the ps1 file.

also
consider the following at 32 for SQL auth connections:

$srv 					= new-object "Microsoft.SqlServer.Management.SMO.Server" $server 
$conContext 				= $srv.ConnectionContext
$conContext.LoginSecure 		= $false
$conContext.Login 			= $username
$conContext.Password 			= $password
$srv 					= New-Object "Microsoft.SqlServer.Management.SMO.Server" $conContext

Thanks for the code.

@TheHandler92

This comment has been minimized.

Copy link

@TheHandler92 TheHandler92 commented Sep 19, 2019

Hello
I have tried to use your template with a SQL auth connections but i have the following error.

Exception lors de l'appel de « EnumScript » avec « 1 » argument(s) : « Fail to connect to X.X.X.X. »
Au caractère C:\Users\XXXX\Documents\test2.ps1:104 : 4
+             $scripter.EnumScript($o)
+             ~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ConnectionFailureException

But it's able to connect as he sees all table, but for each table when it tries to output the data, i have this same error.
Does the loop needs another connexion inside?
I'm a begginner in powershell so i'm a little bit lost !

@badmotorfinger

This comment has been minimized.

Copy link
Owner Author

@badmotorfinger badmotorfinger commented Sep 22, 2019

It only needs one connection at the start of the script.

I'm not sure what your issue is. If it can connect to the server but not get table metadata it could be related to permissions.

@TheHandler92

This comment has been minimized.

Copy link

@TheHandler92 TheHandler92 commented Sep 24, 2019

Thanks for your answer!

For now i'm using it on the main server.
But i think it's coming from trying to connect as my server is not on my "network"

Anyway , do you have any idea how to extract only the table i need?
For example i have a file txt where i have put the name of the table i need

Thanks in advance

@TheHandler92

This comment has been minimized.

Copy link

@TheHandler92 TheHandler92 commented Oct 7, 2019

Hello

I'm still struggling with this code
So i'm using this code from a remote computer connected to a DBserver
So i could connect to my server this time
The script is able to see the DB, the table, the view. i can see everything when i check the variables
He creates the folders but there is no data
I have check the rights of the folder
I'm admin of my computer
I have even put the group "everyone" on the folder
Have you test remotely on your side?

Kind Regards

Maxime

@badmotorfinger

This comment has been minimized.

Copy link
Owner Author

@badmotorfinger badmotorfinger commented Oct 9, 2019

Sorry mate, I haven't used this script for years so I have no idea anymore :)

@mcbos

This comment has been minimized.

Copy link

@mcbos mcbos commented Oct 30, 2019

Hi Maxime,

I changed the script to my needs.
This is working remotely for me, hope it works for you too.

gist

Kind regards,

Martijn

@badmotorfinger

This comment has been minimized.

Copy link
Owner Author

@badmotorfinger badmotorfinger commented Oct 30, 2019

Fantastic! 😃

@TheHandler92

This comment has been minimized.

Copy link

@TheHandler92 TheHandler92 commented Nov 12, 2019

Hi Maxime,

I changed the script to my needs.
This is working remotely for me, hope it works for you too.

gist

Kind regards,

Martijn

Thank you so much mcbos !! it works great !

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.