One of the biggest headaches in most development groups that do any sort of SQL Server work is that of source control.  There are some commercial products that attempt to solve this headache, and from what I understand, some of them do a pretty decent job.  The only one that I have personal experience with is Microsoft’s Team Foundation doohickey, and I walked away from that unimpressed.

Being a “do-it-yourself” kind of guy, and looking for new and interesting ways to use Powershell, I’ve built a (pretty slick, if I say so myself) method of AUTOMATICALLY version-controlling my database objects.  No muss, no fuss, it just works, and nobody needs to remember to do anything.

This will be the first of a series of articles describing what my process does, how it does it, why it does it, and at the end, you’ll have a functional Powershell script that you can use yourself.  All of my examples are specific to Perforce, since that’s what we use here, but should be easily adaptable to your own system.

Powershell makes it very easy to connect to a SQL Server database, and to manipulate the objects within that database.  Virtually anything that you can do through Management Studio, you can also do through Powershell.

Below is the beginnings of our source control script.  In this example, the script will simply connect to the database of your choice, and will loop through certain objects in that database, generating SQL scripts for those objects.

#Set server and database names here
$server = "REALServer"
$dbname = "REALDB"
#Create SMO connection to DB
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
    $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $server
    $db = $SMOserver.databases[$dbname]
#Build collection of objects to script
$Objects = $db.Tables
$Objects += $db.Views
$Objects += $db.StoredProcedures
$Objects += $db.UserDefinedFunctions
#Set destination for generated scripts
$SavePath = "C:\temp\db\Source\" + $($dbname)
#Loop through the object collection
foreach ($ScriptThis in $Objects | where {!($_.IsSystemObject)}) {
#Create Scripter object
    $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
#Set options for generated scripts
    $scriptr.Options.AppendToFile = $True
    $scriptr.Options.AllowSystemObjects = $False
    $scriptr.Options.ClusteredIndexes = $True
    $scriptr.Options.DriAll = $True
    $scriptr.Options.ScriptDrops = $False
    $scriptr.Options.IncludeHeaders = $False
    $scriptr.Options.ToFileOnly = $True
    $scriptr.Options.Indexes = $True
    $scriptr.Options.Permissions = $True
    $scriptr.Options.WithDependencies = $False
#Create destination folder and subfolder, if necessary
    $TypeFolder=$ScriptThis.GetType().Name
    if ((Test-Path -Path "$SavePath\$TypeFolder") -eq "true")
            {"Scripting Out $TypeFolder $ScriptThis"}
        else {new-item -type directory -name "$TypeFolder"-path "$SavePath"}
    $ScriptFile = $ScriptThis -replace "\[|\]"
#Set output filename for script
    $scriptr.Options.FileName = "$SavePath\$TypeFolder\$ScriptFile.SQL"
#Generate script for this object
    $scriptr.Script($ScriptThis)
}
#Second loop through tables to script triggers
foreach ($Table in $db.Tables | where {!($_.IsSystemObject)}) {
#Loop through triggers on this table
    foreach ($ScriptThis in $Table.Triggers) {
#Create Scripter object
        $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
#Set options for generated scripts
        $scriptr.Options.AppendToFile = $True
        $scriptr.Options.AllowSystemObjects = $False
        $scriptr.Options.ClusteredIndexes = $True
        $scriptr.Options.DriAll = $True
        $scriptr.Options.ScriptDrops = $False
        $scriptr.Options.IncludeHeaders = $False
        $scriptr.Options.ToFileOnly = $True
        $scriptr.Options.Indexes = $True
        $scriptr.Options.Permissions = $True
        $scriptr.Options.WithDependencies = $False
#Create destination folder and subfolder, if necessary
$TypeFolder=$ScriptThis.GetType().Name
        if ((Test-Path -Path "$SavePath\Trigger") -eq "true")
                {"Scripting Out Trigger $ScriptThis"}
            else {new-item -type directory -name "Trigger"-path "$SavePath"}
        $ScriptFile = $ScriptThis -replace "\[|\]"
#Set output filename for script
        $scriptr.Options.FileName = "$SavePath\Trigger\$ScriptFile.SQL"
#Generate script for this trigger
        $scriptr.Script($ScriptThis)
}
}