產生資料結構

.ps1

# local directory to save build-scripts to
$Filepath = 'D:\MyScriptsDirectory' 

# server name and instance
$DataSource = '127.0.0.1'

# the database to copy from
$Database = 'databaseName'

# get today date
$today = Get-Date -Format yyyy_MM_dd

# Create the directory if it doesn't exist
$homedir = "$Filepath\"
if (!(Test-Path -path $homedir)) {
  Try { New-Item $homedir -type directory | out-null }  
  Catch [system.exception] {
    Write-Error "error while creating '$homedir' $_"
    return
  }  
}

# set "Option Explicit" to catch subtle errors
set-psdebug -strict

# you can opt to stagger on, bleeding, if an error occurs
$ErrorActionPreference = "stop" 

# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$ms = 'Microsoft.SqlServer'
$v = [System.Reflection.Assembly]::LoadWithPartialName( "$ms.SMO")

if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
  [System.Reflection.Assembly]::LoadWithPartialName("$ms.SMOExtended") | out-null
}

$My = "$ms.Management.Smo" #

$mySrvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$mySrvConn.ServerInstance = "$DataSource"
$mySrvConn.MultipleActiveResultSets = $true
$mySrvConn.LoginSecure = $false
# login database user name
$mySrvConn.Login = "loginName"
# login database password
$mySrvConn.Password = "123456"
$mySrvConn.DatabaseName = "$Database"

$s = new-object ("$My.Server") $mySrvConn

if ($s.Version -eq $null ) {Throw "Can't find the instance $Datasource"}
$db = $s.Databases[$Database] 
if ($db.name -ne $Database) {Throw "Can't find the database '$Database' in $Datasource"};

$transfer = new-object ("$My.Transfer") $db
$CreationScriptOptions = new-object ("$My.ScriptingOptions") 
# yes, we want these
$CreationScriptOptions.ExtendedProperties = $true 
# and all the constraints 
$CreationScriptOptions.DRIAll = $true 
# Yup, these would be nice
$CreationScriptOptions.Indexes = $true 
# This should be included when scripting a database
$CreationScriptOptions.Triggers = $true 
# this only goes to the file
$CreationScriptOptions.ScriptBatchTerminator = $true 
# of course
$CreationScriptOptions.IncludeHeaders = $true;
#no need of string output as well
$CreationScriptOptions.ToFileOnly = $true 
# not necessary but it means the script can be more versatile
$CreationScriptOptions.IncludeIfNotExists = $true 
$CreationScriptOptions.Filename = "$($FilePath)\$($Database)_$($today)_Build.sql"; 
$transfer = new-object ("$My.Transfer") $s.Databases[$Database]
 
# tell the transfer object of our preferences
$transfer.options = $CreationScriptOptions 
$transfer.ScriptTransfer()
"All done"