How to Script All Tables From a Database with PowerShell

PROBLEM: You need to script all the tables from a database.  Each table needs to be on its own file so they can be added to a source control repository.

SOLUTION: The use of SQL Server PowerShell Extensions (SQLPSX) facilitates the solution I’m about to show. The first example is a straight to the point, just DDL for the table without indexes, permissions, or any other scripting options. I used the Get-SQLDatabase, Get-SQLTable and Get-SQLScripter functions from SQLPSX to script out each table from the database into their own file.

CLEAR
$sServer = "TUIRA" 
$sDatabase = "AdventureWorks"
$oTables = Get-SqlDatabase $sServer $sDatabase|Get-SqlTable

foreach ($oTable in $oTables){
    $oT = $oTable.Name
    $oS = $oTable.Schema
    $DDL = Get-SqlScripter $oTable
    Out-File -FilePath "E:AdventureWorks$oS`_$oT.sql" `
        -InputObject $DDL
}

Next, I extended the initial script by adding a reference to the SMO scripting class SQLScriptionOptions.  Now, I can script all primary key, indexes, foreign keys, default values and more.  Look for “ScriptingOptions Members” in Books Online or the Internet and you will find under public properties all the possible things you can include or exclude from your DDL scripts.

Here, I chose to NOT include permissions and to include the clustered index, regular indexes, triggers, and schemas.

CLEAR
$sServer = "TUIRA"
$sDatabase = "AdventureWorks"

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

$oTables = Get-SqlDatabase $sServer $sDatabase|Get-SqlTable

foreach ($oTable in $oTables){
    $oT = $oTable.Name
    $oS = $oTable.Schema
    $DDL = Get-SqlScripter $oTable -scriptingOptions $oSO
    Out-File -FilePath "E:AdventureWorks$oS`_$oT.sql" `
        -InputObject $DDL
}