CODEWITHSUNDEEP
powershell
MarkR
MarkR

Reputation: 23

Generate a SQL create table script using powershell

I want to use powershell to be able to quickly generate a SQL script with create table statements that are able to recreate all tables in an existing database. Only thing is that I want to tweak some options, such as turn identities off.

I just cannot figure out how to do this!

I have already come as far as to set a $server variable, and to set a variable called $tables to get all tables in that particular database. Then I use a loop: foreach ($table in in $tables) {$table.Script() }

This works, but I just don't know how to add scripting options, such as NoIdentities = $True

Can anyone help me out?

Upvotes: 2

Views: 3265

Answers (2)

TD7
TD7

Reputation: 51

If you're still looking for a solution, may I suggest this little script I've been using with a single table. You should be able to update it to support multiple tables fairly easily. Notice line "$scripter.Options.NoIdentities = $true;" below.

param
(
  [string] $server,
  [string] $database,
  [string] $schema,
  [string] $table
)

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database")
$tbl = New-Object ("Microsoft.SqlServer.Management.SMO.Table")
$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server)
$db = $srv.Databases[$database]
$tbl = $db.tables | Where-object {$_.schema -eq $schema-and$_.name -eq $table}

$scripter.Options.ScriptSchema = $true;
$scripter.Options.ScriptData = $false;

$scripter.Options.NoCommandTerminator = $false;
$scripter.Options.NoCollation = $true;
$scripter.Options.NoIdentities = $true;

$scripter.EnumScript($tbl)

Save it to "table.ps1" and execute from within PowerShell by passing param values:

& table.ps1 -server SERVER\INSTANCE -database MyDB -schema dbo -table MyTable

Let me know if it works.

Upvotes: 0

Walter Mitty
Walter Mitty

Reputation: 18940

I once had to do a lot of repetitive type work, including generate SQL scripts for every table in a database. So I developed a general purpose tool that is good for this type of work. I am including the tool, as is, and a sample run of the tool intended to produce a series of grant commands for each table, and each category of database user.

My tool runs off of CSV files rather than off of the database directly. I found it fairly easy to generate CSV files and templates for a lot of different tasks. Your mileage may vary. Maybe you can start with this tool and adapt it to your needs.

Here is the tool, and a sample run.

<#
.SYNOPSIS
    Generates multiple expansions of a template,
    driven by data in a CSV file.
.DESCRIPTION
    This function is a table driven template tool. 

    It generates output from a template and
    a driver table.  The template file contains plain
    text and embedded variables.  The driver table 
    (in a csv file) has one column for each variable, 
    and one row for each expansion to be generated.
#>
function Expand-csv {


   [CmdletBinding()]
   Param(
      [Parameter(Mandatory=$true)]
      [string] $driver,
      [Parameter(Mandatory=$true)]
      [string] $template
   )
   Process
   {
      $xp = (Get-Content $template) -join "`r`n"

      Import-Csv $driver | % {
         $_.psobject.properties | % {Set-variable -name $_.name -value $_.value}
         $ExecutionContext.InvokeCommand.ExpandString($xp) 
         }
   }
}

# Now do a sample run of Expand-csv
# then display inputs and output

Expand-csv grants.csv grants.tmplt > grants.sql
get-content grants.tmplt
import-csv grants.csv
get-content grants.sql

Here is the result of running the above:

PS> C:\Users\David\Software\Powershell\test\sample.ps1


grant $privs 
   on $table 
   to $user;



privs       table       user                
-----       -----       ----                
ALL         Employees   DBA                 
READ        Employees   Analyst             
READ, WRITE Employees   Application         
ALL         Departments DBA                 
READ        Departments Analyst, Application


grant ALL 
   on Employees 
   to DBA;




grant READ 
   on Employees 
   to Analyst;




grant READ, WRITE 
   on Employees 
   to Application;




grant ALL 
   on Departments 
   to DBA;




grant READ 
   on Departments 
   to Analyst, Application;





PS>

In real life, I have the tool defined in my $profile file, so that it's available whenever I'm in powershell.

I'm not sure how well this applies to your case. This doesn't address the exact situation you describe, but you may be able to adapt the technique.

Upvotes: 1

Related Questions