script powershell to generate ddl table

Can you show an example? "DDL tables" is something I cannot decode. DDL creates or changes database object definitions (add columns, add table, add constraint, ...).

Hello to Generate script for Each table définition containing column and filegroup.

Thanks

The features of SSMS to generate scripts is not sufficient?

No I have to use PowerShell. Thanks

This can get pretty involved. A "simple" attempt, certainly dumping too much, is this:

$server = "MSSQLServer\Instance"
$srcDB  = "TestDB"
$dump   = "C:\Temp\EE\TestDB.sql"

# Stop on any error
$ErrorActionPreference = "stop"

[void] [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
[void] [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SQlServer.SMOExtended')
$xfer = [Microsoft.SqlServer.Management.SMO.Transfer] ([Microsoft.SqlServer.Management.SMO.Server] $server).Databases[$srcDB]

# Set export options.
$opts = New-Object Microsoft.SqlServer.Management.SMO.ScriptingOptions
$opts.Filename              = $dump
$opts.ToFileOnly            = $true
$opts.AllowSystemObjects    = $false
$opts.Statistics            = $false
$opts.ScriptDataCompression = $false

$xfer.options    = $opts
$xfer.ScriptTransfer()

Select allOpen in new window

It is based on scripts and explanations found at https://www.simple-talk.com/sql/database-administration/automated-script-generation-with-powershell-and-smo/. That site also shows how to get specific objects only (see "Automated scripting of objects").

Thanks

Hello,

I search to script all tables with SMO from $liste_table but only the first table is generated on the file tbl_ddl.sql

$SQLSERVER="TEST\TEST"
$Database="DB"
$Schema="dbo"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "$SQLSERVER"
$db=$s.Databases["$Database"]
$liste_table = (& sqlcmd -E -S $SQLSERVER -h -1 -b -W -Q "set nocount on;select object_name from [DBA].[dbo].[ddl_configuration] where new_fg_name <> 'NULL' and  new_fg_name <> ''")

echo $liste_table
foreach($table in $liste_table)
{

$tbls = $db.Tables
$tbl = $tbls  | where { $_.Schema -eq "$Schema" -and $_.Name -eq $table }

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
$scrp.Options.DriPrimaryKey=$true
$scrp.Options.FileName = "D:\MSSQLSERVER\scripts\DEV\ddl\tbl_ddl.sql"
$scrp.Options.ToFileOnly = $True
$scrp.Script($tbl)
}

How can I resolve, please ?

Thanks

Regards

Does the echo print more than one table? If yes, then everything should work, but you have to use different file names for each table. As-is you are overwriting tbl_ddl.sql, so only the last table's DDL code remains. Or you set $scrp.Options.AppendToFile = $true.

Yes, but the following error is generated
Multiple ambiguous overloads found for "Script" and the argument count: "1".
At line:8 char:1
+ $scrp.Script($tbl)
+ ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

Which one of the two suggestions works?