Scripting out a database can be done from SQL Server Management Studio, but this is a manual operation. When you are serving a small army of developers, they need a definition of the production database on a regular basis. Doing that by SQL Server Management Studio quickly gets boring.
There are third party tools like Red Gate SQL Compare that can generate a schema snapshot or a direct comparison, but such a tool is expensive for a simple task like this. In many other situations I am really happy with such tools, but that is another story.
This PowerShell script generates the T-SQL script to create a database and its structure. The script can be edited and reviewed before execution, and can serve as documentation in a Change procedure.
[string]$SqlServerName = '(local)'
[string]$DbName = 'AdventureWorks'
[string]$SqlBatchSeperator = 'GO' # SQL Server Management Studio default: GO
"/*`nT-SQL script to define the structure of the database '$($DbName)' on the server '$($SqlServerName)'."
"Script generated $($(Get-Date).ToUniversalTime().ToString('s')) UTC"
" by the user $([Security.Principal.WindowsIdentity]::GetCurrent().Name)"
" on the computer $($env:COMPUTERNAME)"
" with the script file '$($MyInvocation.InvocationName)'"
"*/"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServerName
$Options = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$Options.IncludeHeaders = $true
$Options.Permissions = $true
"`n--*** Logins ***"
$Server.Logins | Where-Object { ($_.Name -ne 'sa') -and ($_.Name -notlike '##MS_Policy*') -and ($_.Name -notlike 'NT *') } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n--*** Database definition ***"
$Server.Databases[$DbName].Script();$SqlBatchSeperator
"`n--EXECUTE [$DbName].dbo.sp_changedbowner @loginame = N'sa', @map = true;`n$SqlBatchSeperator"
"`nALTER AUTHORIZATION ON DATABASE::[$DbName] TO [sa];`n$SqlBatchSeperator"
"`nUSE [$DbName];`n$SqlBatchSeperator"
"`n`n--*** Userdefined applicationroles ***"
$Server.Databases[$DbName].ApplicationRoles | Where-Object { $_.IsFixedRole -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined databaseroles ***"
$Server.Databases[$DbName].Roles | Where-Object { $_.IsFixedRole -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined schemas ***"
$Server.Databases[$DbName].Schemas | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
$Options.IncludeDatabaseRoleMemberships = $true
"`n`n--*** Database users ***"
$Server.Databases[$DbName].Users | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
$Options.DriAll = $true
$Options.Indexes = $true
"`n`n--*** Userdefined assemblies ***"
$Server.Databases[$DbName].Assemblies | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined certificates ***"
$Server.Databases[$DbName].Certificates | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined defaults ***"
$Server.Databases[$DbName].Defaults | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined fulltext catalogs ***"
$Server.Databases[$DbName].FullTextCatalogs | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined rules ***"
$Server.Databases[$DbName].Rules | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined stored procedures ***"
$Server.Databases[$DbName].StoredProcedures | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined tables with indexes ***"
$Server.Databases[$DbName].Tables | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined Triggers ***"
$Server.Databases[$DbName].Triggers | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined functions ***"
$Server.Databases[$DbName].UserDefinedFunctions | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined datatypes ***"
$Server.Databases[$DbName].UserDefindTypes | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined views ***"
$Server.Databases[$DbName].Views | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }The script covers the basics, and I think that it is easy to fit it to a equal task.
The script can be used in a Visual Studio solution to compare with a database in development. Such a comparison can give a deployment script for a higher and isolated environment.
Posts
