How to Generate a Create Table DDL Script Along With Its Related Tables
- by Compudicted
Have you ever wondered when creating table diagrams in SQL Server Management Studio (SSMS) how slickly you can add related tables to it by just right-clicking on the interesting table name? Have you also ever needed to script those related tables including the master one? And you discovered you have dozens of related tables? Or may be no SSMS at your disposal? That was me one day. Well, creativity to the rescue! I Binged and Googled around until I found more or less what I wanted, but it was all involving T-SQL, yeah, a long and convoluted CROSS APPLYs, then I saw a PowerShell solution that I quickly adopted to my needs (I am not referencing any particular author because it was a mashup): 1: ###########################################################################################################
2: # Created by: Arthur Zubarev on Oct 14, 2012 #
3: # Synopsys: Generate file containing the root table CREATE (DDL) script along with all its related tables #
4: ###########################################################################################################
5:
6: [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
7:
8: $RootTableName = "TableName" # The table name, no schema name needed
9:
10: $srv = new-Object Microsoft.SqlServer.Management.Smo.Server("TargetSQLServerName")
11: $conContext = $srv.ConnectionContext
12: $conContext.LoginSecure = $True
13: # In case the integrated security is not used uncomment below
14: #$conContext.Login = "sa"
15: #$conContext.Password = "sapassword"
16: $db = New-Object Microsoft.SqlServer.Management.Smo.Database
17: $db = $srv.Databases.Item("TargetDatabase")
18:
19: $scrp = New-Object Microsoft.SqlServer.Management.Smo.Scripter($srv)
20: $scrp.Options.NoFileGroup = $True
21: $scrp.Options.AppendToFile = $False
22: $scrp.Options.ClusteredIndexes = $False
23: $scrp.Options.DriAll = $False
24: $scrp.Options.ScriptDrops = $False
25: $scrp.Options.IncludeHeaders = $True
26: $scrp.Options.ToFileOnly = $True
27: $scrp.Options.Indexes = $False
28: $scrp.Options.WithDependencies = $True
29: $scrp.Options.FileName = 'C:\TEMP\TargetFileName.SQL'
30:
31: $smoObjects = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection
32: Foreach ($tb in $db.Tables)
33: {
34: Write-Host -foregroundcolor yellow "Table name being processed" $tb.Name
35:
36: If ($tb.IsSystemObject -eq $FALSE -and $tb.Name -eq $RootTableName) # feel free to customize the selection condition
37: {
38: Write-Host -foregroundcolor magenta $tb.Name "table and its related tables added to be scripted."
39: $smoObjects.Add($tb.Urn)
40: }
41: }
42:
43: # The actual act of scripting
44: $sc = $scrp.Script($smoObjects)
45:
46: Write-host -foregroundcolor green $RootTableName "and its related tables have been scripted to the target file."
Enjoy!
