Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

script powershell to generate ddl table

Posted on 2016-07-18
10
Medium Priority
?
222 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 71

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 71

Expert Comment

by:Qlemo
ID: 41717697
The features of SSMS to generate scripts is not sufficient?
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

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

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
 

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 71

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 71

Accepted Solution

by:
Qlemo earned 2000 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 71

Expert Comment

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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

647 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