Solved

script powershell to generate ddl table

Posted on 2016-07-18
10
90 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article will help you understand what HashTables are and how to use them in PowerShell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

861 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

22 Experts available now in Live!

Get 1:1 Help Now