0

Looking for a way to generate individual scripts for each table and include any relationships or extended properties. (After getting this working I will try to generate scripts for stored procedure, and function)

I am aware of the Sql-Server UI Generator (Database=>Tasks=>Generate Scripts) but that does one big file, not individuals. If there is a way to make this produce individual files (with out doing them 1 at a time) that would be best.

I have used Powershell package DBATools with some limited success. The following will make a file that contains create scripts for the table and the table's extended property but not the column extended properties.

$server = "sql01"
$database = "MyDatabase"
$table = "MyTable"
Get-DbaDbTable -SqlInstance $server -Database $database -Table $table | Export-DbaScript -FilePath ($database + "\" + $table +".sql")
Get-DbaDbTable -SqlInstance $server -Database $database -Table $table | Get-DbaExtendedProperty | ForEach-Object { Export-DbaScript -InputObject $_ -FilePath ($database + "\" + $table +".sql") -Append }
nonoandy
  • 120
  • 15
  • 2
    *"but that does one big file, not individuals."*This isn't true, it does both; there is a "One script file per object" option as well as a "Single script file" option. – Thom A Mar 24 '22 at 15:40
  • Wow, You are correct! I can't believe I have over looked this. – nonoandy Mar 24 '22 at 15:59

2 Answers2

1

The answer was given by Larnu in the commnets.

The comment pointed out the option to save scripts individually and I have been over looking it for years.

enter image description here

nonoandy
  • 120
  • 15
1

While you found the option in SSMS, I wanted to say that this is also possible using the dbatools approach you tried. The "secret sauce" is specifying a ScriptingOptions object that controls the scripting behavior.

$so = New-DbaScriptingOption;
$so.ExtendedProperties = $true;

foreach ($table in Get-DbaDbTable -SqlInstance . -Database AdventureWorks2019){
   $path = '{0}.{1}.sql' -f $table.Schema, $table.Name;
   Export-DbaScript -InputObject $table -FilePath $path -ScriptingOptionsObject $so;
}
Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • I compare the two generated files and both do what I need. I like the dbatools gives me more controls with the file naming and placement and I can adjust the script to do more than tables easy. I did notice Sql Server added in some additional properties about the table (like default settings) but they are not relevant to me currently. – nonoandy Mar 28 '22 at 13:55
  • In re: default settings (I assume you're talking about things like collations), I think those are included regardless. AFAIK, there isn't anywhere in the schema that specifies how a given setting was set (i.e. explicitly via the DDL that created/altered the object or implicitly from the default setting) so scripting just scripts it out. – Ben Thul Mar 28 '22 at 18:19