Generate Scripts for Tables and all Extended Properties-CodePudding

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 }

CodePudding user response：

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.

CodePudding user response：

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;
}