PROBLEM: You need to copy the table definition for all tables in a database between two SQL servers.

SOLUTION: One of the requirements is to use PowerShell and I will use it in combination with SQLPSX. Yes, I will use SQLPSX again and you probably have noticed I use it in all my PowerShell scripts that access MS SQL Server –I’m a fan, what else can I say.

There are a few highlighted lines of code I want to mention because I had a bit of trouble while scripting tables with dependencies on user defined data types and partitioned tables. Line 5 and 6 are string variables assigments for the target server and database. On line 13, I added the scripting option “NoFileGroup” because the source database was set up using multiple filegroups and the script failed when these file groups were not available on the target server; the target server in this example will have a single file group. On line 15, I added the “WithDependencies” which helped with the creation of partitioned table functions and schemas. And on line 16, I used the “IncludeIfNotExists” in order to avoid errors if the database objects already exists.

CLEAR 

$sSRCServer = "TUIRADEVELOPMENT" 
$sSRCDatabase = "AdventureWorks" 
$sTRGServer = "TUIRA" 
$sTRGDatabase = "AdventureWorks" 

$oSO = New-SqlScriptingOptions 
$oSO.Permissions = $false 
$oSO.ClusteredIndexes = $true 
$oSO.Indexes = $true 
$oSO.Triggers = $true 
$oSO.NoFileGroup = $true
$oSO.NonClusteredIndexes = $true 
$oSO.WithDependencies = $true
$oSO.IncludeIfNotExists = $true

$oTables = Get-SqlDatabase $sSRCServer $sSRCDatabase|Get-SqlTable 

foreach ($oTable in $oTables){  
    $oT = $oTable.Name  
    $oS = $oTable.Schema  
    $DDL = Get-SqlScripter $oTable -scriptingOptions $oSO 
    invoke-sqlcmd -ServerInstance "$sTRGServer" -Database "$sTRGDatabase" `
          -Query "$DDL" 
}

This solution can easily be converted to a function with optional parameters that map to scripting options. The target variables for server and/or database could hold arrays for copying the tables definition to multiple servers. A form could also be developed using WPF to make it user friendly. What do you think?

Related Links

• How to Script All Tables from a Database with PowerShell.
• How to Script All Stored Procedures from a Database with PoserShell.

En Español