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
}

How to Change File Extension for all Files in a Folder

PROBLEM: You have a directory with many files and would like to change the extension on many of them without having to do one by one.

SOLUTION: You can solve this problem with PowerShell and in less than 3 lines of code.

CLEAR CD [PATH TO FILE LOCATION] Get-ChildItem *.[FILE EXTENSION]|Rename-Item –NewName {$_.Name –replace "[FROM VALUE]","[TO VALUE]"}

I always like to clear my console and that is what the CLEAR command does. I will then change to the directory where the files are located and execute my one liner that will change the file extensions of many files. Here’s an example of how to change the file extensions from .TAB to .SQL.

CLEAR
CD C:DatabaseScripts
Get-ChildItem *.TAB|Rename-Item -NewName { `
    $_.Name -replace "TAB","SQL"
}

In Spanish

Review SQL Server 2008 Administration in Action

I got a copy of this book back in September of 2009 because I wanted to know more about the hardware configuration of production database servers. Specifically, I wanted to know about best practices and recommendations in the areas of storage, memory and CPU. While at the store, I read chapter 2 “Storage and System Sizing” which covers from what SATA means to areas such as RAID, SAN, solid state drives, and best practice considerations. I purchased this book just for the content in chapter 2 and had zero expectations for the rest.

After reading the remainder of the book, I commend Rod College and the team behind this volume for their fantastic job. The material is accurate, current and invaluable to DBAs at any level. You’ll get a wealth of information from new features found in MS SQL 2008 to concepts you should already know but you are not familiar with in MS SQL 2008.