How do we document databases in SQL server?

SQL Server Documentation

The process of documenting a SQL Server database is a complete and continuous process that should start during the database design and development phases and continue during all database-related life cycles in a way that ensures having an up-to-date version of the database documentation that reflects reality at any. With the following PowerShell Script I can generate a Create Table scripts for single table or for all tables in the dbo schema. The script contains a Create table command, primary keys and indexes. Foreign keys are added as comments. The extended properties of tables and table columns are added as comments. Yes multi line properties are supported.

The script is tuned to my personal coding style.

• no individual collations for single columns.
  
• currently it requires Sql Server Authentication.
  

Here is the complete code to turn the extended properties into a good plain old ASCII document (BTW it is valid sql to recreate your tables):

function Get-ScriptForTable { param ( $server, $dbname, $user, $password, $filter ) [System.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | out-null $conn = new-object "Microsoft.SqlServer.Management.Common.ServerConnection" $conn.ServerInstance = $server $conn.LoginSecure = $false $conn.Login = $user $conn.Password = $password $conn.ConnectAsUser = $false $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $conn $Scripter = new-object ("Microsoft.SqlServer.Management.Smo.Scripter") #$Scripter.Options.DriAll = $false $Scripter.Options.NoCollation = $True $Scripter.Options.NoFileGroup = $true $scripter.Options.DriAll = $True $Scripter.Options.IncludeIfNotExists = $False $Scripter.Options.ExtendedProperties = $false $Scripter.Server = $srv $database = $srv.databases[$dbname] $obj = $database.tables $cnt = 1 $obj | % { if (! $filter -or $_.Name -match $filter) { $lines = @() $header = "---------- {0, 3} {1, -30} ----------" -f $cnt, $_.Name Write-Host $header "/* ----------------- {0, 3} {1, -30} -----------------" -f $cnt, $_.Name foreach( $i in $_.ExtendedProperties) { "{0}: {1}" -f $i.Name, $i.value } "" $colinfo = @{} foreach( $i in $_.columns) { $info = "" foreach ($ep in $i.ExtendedProperties) { if ($ep.value -match "`n") { "----- Column: {0} {1} -----" -f $i.name, $ep.name $ep.value } else { $info += "{0}:{1} " -f $ep.name, $ep.value } } if ($info) { $colinfo[$i.name] = $info } } "" "SELECT COUNT(*) FROM {0}" -f $_.Name "SELECT * FROM {0} ORDER BY 1" -f $_.Name "--------------------- {0, 3} {1, -30} ----------------- */" -f $cnt, $_.Name "" $raw = $Scripter.Script($_) #Write-host $raw $cont = 0 $skip = $false foreach ($line in $raw -split "

") { if ($cont -gt 0) { if ($line -match "^)WITH ") { $line = ")" } $linebuf += ' ' + $line -replace " ASC", "" $cont-- if ($cont -gt 0) { continue } } elseif ($line -match "^ CONSTRAINT ") { $cont = 3 $linebuf = $line continue } elseif ($line -match "^UNIQUE ") { $cont = 3 $linebuf = $line $skip = $true continue } elseif ($line -match "^ALTER TABLE.*WITH CHECK ") { $cont = 1 $linebuf = "-- " + $line continue } elseif ($line -match "^ALTER TABLE.* CHECK ") { continue } else { $linebuf = $line } if ($linebuf -notmatch "^SET ") { if ($linebuf -match "^)WITH ") { $lines += ")" } elseif ($skip) { $skip = $false } elseif ($linebuf -notmatch "^s*$") { $linebuf = $linebuf -replace "]|[", "" $comment = $colinfo[($linebuf.Trim() -split " ")[0]] if ($comment) { $comment = ' -- ' + $comment } $lines += $linebuf + $comment } } } $lines += "go" $lines += "" $block = $lines -join "`r`n" $block $cnt++ $used = $false foreach( $i in $_.Indexes) { $out = '' $raw = $Scripter.Script($i) #Write-host $raw foreach ($line in $raw -split "

") { if ($line -match "^)WITH ") { $out += ")" } elseif ($line -match "^ALTER TABLE.* PRIMARY KEY") { break } elseif ($line -match "^ALTER TABLE.* ADD UNIQUE") { $out += $line -replace "]|[", "" -replace " NONCLUSTERED", "" } elseif ($line -notmatch "^s*$") { $out += $line -replace "]|[", "" -replace "^s*", "" ` -replace " ASC,", ", " -replace " ASC$", "" ` <#-replace "dbo.", "" #> ` -replace " NONCLUSTERED", "" } $used = $true } $block = "$out;`r`ngo`r`n" $out } if ($used) { "go" } } } }

You can either script thecomplete dbo schema of a given database

Get-ScriptForTable 'localhost' 'MyDB' 'sa' 'toipsecret' | Out-File "C:	empCreate_commented_tables.sql"

Or filter for a single table

Get-ScriptForTable 'localhost' 'MyDB' 'sa' 'toipsecret' 'OnlyThisTable'