Dump common user defined MSSQL database objects (including table structures, procedures, views, etc.) into SQL script files and place into folders. Useful when working with source control tools like GIT.

DumpNoData.ps1

# Script Name: DumpNoData
# Version: 1.0.1 (2. June, 2014)
# Author: Sveinn Steinarsson

<#
.SYNOPSIS
	Dumps the structure of a MSSQL database into SQL scripts files, including tables, procedures, triggers, etc.
.DESCRIPTION
	Common user defined database objects are dumped into SQL script files and placed into folders.
	The main purpose is to be used with version control tools such as GIT.
	Features include:
		* Excluding certain database objects based on name.
		* Only writing files if they have more recent modify time in database.
.PARAMETER Server
    Hostname or IP of the server.
.PARAMETER Database
	Name of the database.
.PARAMETER Login
	Login to the server (if not using integrated security).
.PARAMETER Password
	Password to the server (if not using integrated security).
.PARAMETER Destpath
	Default is a subfolder named after the database under the running path.
.PARAMETER Exclude
	File containing a list of regular expressions (one per line) to exclude matching database objects.
.PARAMETER Alwayswrite
	Always write files ignoring the last modified time in the database.
.PARAMETER Verbose
	Write out what database object is being processed	
.EXAMPLE
	.\DumpNoData.ps1 -server myserver -database AdventureWorks
.EXAMPLE	
	.\DumpNoData.ps1 -server myserver -database AdventureWorks -login Bob -password keyword123 -alwayswrite -verbose
.EXAMPLE
	.\DumpNoData.ps1 -server myserver -database AdventureWorks -destpath C:\Tmp\Database -exclude exlude.txt
	exclude.txt might contain:
	Department
	(?i)_bak
.NOTES
    Author: Sveinn Steinarsson
	Based on SmoDb by Vince Panuccio https://gist.github.com/vincpa/1755925
#>


Param (
	[string]$server = $(Throw "Please provide a server"),
	[string]$database = $(Throw "Please provide a database"),
	[string]$login,
	[string]$password,
	[string]$destpath,
	[string]$exclude,
	[switch]$alwayswrite,
	[switch]$verbose
)

$startTime = (Get-Date)

if ($destpath -eq "") {
	$scriptPath	= split-path -parent $MyInvocation.MyCommand.Definition
	$destpath	= $scriptPath + "\" + $database
}

if (-not (Test-Path $destpath)) {
	[System.IO.Directory]::CreateDirectory($destpath) | Out-Null
}

if ($exclude -ne "") {
	$excludePatterns = Get-Content $exclude
} else {
	$excludePatterns = @()
}

# Local file structure
$tablePath			= "$destpath\Tables\"
$storedProcsPath	= "$destpath\Programmability\Stored Procedure\"
$viewsPath			= "$destpath\Views\"
$udfsPath			= "$destpath\Programmability\Functions\"
$textCatalogPath	= "$destpath\Storage\Full Text Catalogs\"
$udttsPath			= "$destpath\Types\User-Defined Table Types\"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | out-null

if ($login -ne "" -and $password -ne "") {
	# Standard SQL User Authentication
	$connectionString = "Server=$server;User Id=$login;Password=$password;"
	$srvConn = New-Object ("Microsoft.SqlServer.Management.Common.ServerConnection")
	$srvConn.ConnectionString = $connectionString	
	$srv	= New-Object ("Microsoft.SqlServer.Management.SMO.Server") ($srvConn)
} else {
	# Trusted Connection
	$srv	= New-Object ("Microsoft.SqlServer.Management.SMO.Server") ($server)
}

$scripter	= New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($srv)

# Get the database and table objects
$db = $srv.Databases[$database]

# Set scripter options to ensure only data is scripted
$scripter.Options.ScriptSchema	= $true
$scripter.Options.ScriptData	= $false

$scripter.Options.NoCommandTerminator	= $false
$scripter.Options.ToFileOnly			= $true
$scripter.Options.AllowSystemObjects	= $false
$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.Triggers				= $true
$scripter.Options.EnforceScriptingOptions	= $true
$scripter.Options.ToFileOnly			= $true

$global:countWrite	= 0
$global:countUpdate	= 0
$global:countSkip	= 0
$global:countExclude= 0


function CopyObjectsToFiles($objects, $outDir) {

	if ($objects -ne $null -and -not (Test-Path $outDir)) {
		# Only create the folder if there are objects to put in it
		[System.IO.Directory]::CreateDirectory($outDir) | Out-Null
	}
	
	foreach ($o in $objects) { 

		if ($o -ne $null) {
			
			$schemaPrefix = ""
			
			if ($o.Schema -ne $null -and $o.Schema -ne "") {
				$schemaPrefix = $o.Schema + "."
			}
			
			$outputfile = $outDir + $schemaPrefix + $o.Name + ".sql"
			$scripter.Options.FileName = $outputfile

			$excludePatterns | ForEach-Object{ 
				if ($o.Name -match $_) {
					if ($verbose) {
						Write-Host "Excluding" $scripter.Options.FileName
					}
					$global:countExclude++
					continue
				}
			}

			if (-not ($alwayswrite) -and (Test-Path $outputfile)) {
				# File exists
				$file = Get-Item $outputfile	
				if ($o.DateLastModified -gt $file.LastWriteTime) {
					if ($verbose) {
						Write-Host "Updating" $scripter.Options.FileName
					}
					$scripter.EnumScript($o)
					$file.LastWriteTime	= $o.DateLastModified
					$global:countUpdate++
				} else {
					if ($verbose) {
						Write-Host "Skipping" $scripter.Options.FileName
					}
					$global:countSkip++
				}
			
			} else {
				# File does not exists. Create it.
				if ($verbose) {
					Write-Host "Writing" $scripter.Options.FileName
				}
				$scripter.EnumScript($o)

				$global:countWrite++
			}

		}
	}
}

$tables			= $db.Tables | Where-object { -not $_.IsSystemObject } 
CopyObjectsToFiles $tables $tablePath

$storedProcs	= $db.StoredProcedures | Where-object { -not $_.IsSystemObject } 
CopyObjectsToFiles $storedProcs $storedProcsPath

$views			= $db.Views | Where-object { -not $_.IsSystemObject } 
CopyObjectsToFiles $views $viewsPath

$udfs			= $db.UserDefinedFunctions | Where-object { -not $_.IsSystemObject } 
CopyObjectsToFiles $udfs $udfsPath

$ftCatlogs		= $db.FullTextCatalogs
CopyObjectsToFiles $ftCatlogs $textCatalogPath

$udtts			= $db.UserDefinedTableTypes
CopyObjectsToFiles $udtts $udttsPath	


Write-Host "==================="
Write-Host "Elapsed Time: $(((Get-Date)-$startTime).totalseconds) seconds"
Write-Host "Finished:" (Get-Date)

Write-Host "Written: $countWrite Updated: $countUpdate Skipped: $countSkip Excluded: $countExclude"
Write-Host "Total:" ($countWrite + $countUpdate + $countSkip + $countExclude)