?
Solved

script powershell to generate ddl table

Posted on 2016-07-18
10
Medium Priority
?
180 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 70

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 70

Expert Comment

by:Qlemo
ID: 41717697
The features of SSMS to generate scripts is not sufficient?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

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

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 70

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 70

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 70

Expert Comment

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

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

752 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