How to Generate a Create Table DDL Script Along With Its Related Tables

Posted by Compudicted on Geeks with Blogs See other posts from Geeks with Blogs or by Compudicted
Published on Sun, 14 Oct 2012 19:14:54 GMT Indexed on 2012/10/15 3:40 UTC
Read the original article Hit count: 243

Filed under:

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!

© Geeks with Blogs or respective owner