Solved

script powershell to generate ddl table

Posted on 2016-07-18
10
70 Views
Last Modified: 2016-07-22
Hello,

I search a powershell script to generate ddl tables for a database.

Thanks

Regards
0
Comment
Question by:bibi92
  • 6
  • 4
10 Comments
 
LVL 68

Expert Comment

by:Qlemo
ID: 41717568
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, ...).
0
 

Author Comment

by:bibi92
ID: 41717601
Hello to Generate script for Each table définition containing column and filegroup.

Thanks
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41717697
The features of SSMS to generate scripts is not sufficient?
0
 

Author Comment

by:bibi92
ID: 41719860
No I have to use PowerShell. Thanks
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41723125
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()

Open 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").
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:bibi92
ID: 41723126
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
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41723199
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.
0
 

Author Comment

by:bibi92
ID: 41723218
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
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 41723303
Somehow the scripter cannot cope with the data type of $tbl. The Script method expects a collection of URN or an array of URNs / SqlSMOObjects. Maybe you just have to use an cast, like
$scrp.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]] $tbl)

Open in new window

or just
$scrp.Script(@($tbl))

Open in new window

to make sure it is an array.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41724583
Which one of the two suggestions works?
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now