As I mentioned earlier, we use Perforce as our source control package, so all of the scripts presented here are written for Perforce.  I’ll try to clearly explain, in terms of source control, the functional parts of the script, so that you can easily transfer this to your own source control package.  They should all offer the same basic features and some sort of command-line interface.  If you are a Perforce user, you may find it helpful to create a “non-expiring” login for this script to use.

Ok, sit up straight, eyes forward, let’s get started…

The first thing that Perforce requires us to do is to create a “change list”, where all changed, added, or deleted scripts will be recorded.  If, at the end of the process, nothing has been changed, added, or deleted, the change list will be discarded.  All it takes is a couple of lines of Powershell script to create the change list and save the number that Perforce assigns us:

#Create new Perforce changelist
$NewChangeListCommand = p4 -u RealP4Login change -o | select-string -pattern change, client, status
$NewChangeListCommand += "Description: SQL Server automated object script"
[int]$changeListNumber = $NewChangeListCommand | p4 -u RealP4Login change -i | %{ $_.split()[1] }

As part of the process, as database objects are being scripted out, we’re going to do one of two things – add the script to Perforce if it’s not in there already, or change the script in Perforce if it is.  We need to check to see if the script already exists, so that we can make that decision:

#Check file status against Perforce
$P4FileStatus = p4 -u RealP4Login fstat $SavePath\$TypeFolder\$ScriptFile.SQL
if ($P4FileStatus)
 {
  #Open Perforce files for edit
  p4 -u RealP4Login sync $SavePath\$TypeFolder\$ScriptFile.SQL
  p4 -u RealP4Login edit -c $changeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL
 }

Now that we know if the script is already in Perforce, we can act accordingly.  If it’s not there, we add it as a new script.  If it IS there, we need to see if the script generated from the database is different than the script currently in Perforce.  There is a powerful “diff” function built in to Perforce, so we’ll use that to compare the “new” script with the current script.  If they match, we’ll simply revert our check-out and move on.  If they don’t match, we’ll check-in the new script.

#Add new files to Perforce
if (!($P4FileStatus))
 {
  p4 -u RealP4Login add -c $ChangeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL
  $ChangeListMessage = $ChangeListMessage + "Added " + $TypeFolder + " " + $ScriptFile + "
"
 }
else
 {
  #"diff" file, revert if unchanged
  $P4FileDiff = p4 -u RealP4Login diff -sa $SavePath\$TypeFolder\$ScriptFile.SQL
  if (!($P4FileDiff))
   {
    echo "Reverting"
    p4 -u RealP4Login revert -c $ChangeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL
   }
  else
   {
    $ChangeListMessage = $ChangeListMessage + "Changed " + $TypeFolder + " " + $ScriptFile + "
"
   }
 }

Finally, to button things back up again, we need to know if there’s actually anything to submit to Perforce.  This is easily done by checking the contents of our change list.  If there’s nothing in the change list, then we know that we didn’t add or change any database scripts.  If that’s the case, we simply discard the change list and we’re done.  Otherwise, we “submit” the change list, which commits the changes to source control.  As an added bonus, we’re going to send an email to the DBA letting him know that some things have changed in his database.

#Check if changelist includes any new/modified files
$P4FilesChanged = p4 -u RealP4Login opened -c $changeListNumber
if ($P4FilesChanged)
 {
  #Submit changelist to Perforce
  p4 -u RealP4Login submit -c $changeListNumber
  #Send summary email
  $Now = Get-Date
  $SMTPserver = "RealSMTPServer"
  $from = "real@real-sql-guy.com"
  $to = "real@real-sql-guy.com"
  $subject = "$dbname database changes submitted to Perforce"
  $emailbody = "<h1>Changelist #$changeListNumber </h1>
<b>Submitted $Now </b>
<hr>$ChangeListMessage "
  $mailer = new-object Net.Mail.SMTPclient($SMTPserver)
  $msg = new-object Net.Mail.MailMessage($from, $to, $subject, $emailbody)
  $msg.IsBodyHTML = $true
  $mailer.send($msg)
 }
else
 {
  #Delete changelist
  p4 -u RealP4Login change -d $changeListNumber
 }

I hope this all makes sense, and that you can adapt it to fit your own source control package.  Here’s the final script, with all of the Perforce integration included:

#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)
#Create new Perforce changelist
$NewChangeListCommand = p4 -u RealP4Login change -o | select-string -pattern change, client, status
$NewChangeListCommand += "Description: SQL Server automated object script"
[int]$changeListNumber = $NewChangeListCommand | p4 -u RealP4Login change -i | %{ $_.split()[1] }
if ($changeListNumber -gt 0)
    {
    echo "P4 changelist number $changeListNumber"
    $ChangeListMessage = ""
 #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 "\[|\]"
  #Check file status against Perforce
  $P4FileStatus = p4 -u RealP4Login fstat $SavePath\$TypeFolder\$ScriptFile.SQL
  if ($P4FileStatus)
   {
    #Open Perforce files for edit
    p4 -u RealP4Login sync $SavePath\$TypeFolder\$ScriptFile.SQL
    p4 -u RealP4Login edit -c $changeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL
   }
  #Set output filename for script
  $scriptr.Options.FileName = "$SavePath\$TypeFolder\$ScriptFile.SQL"
  #Add commented USE statement for deployment validation tool
  "USE $dbname" >  "$SavePath\$TypeFolder\$ScriptFile.SQL"
  #Add existing object handler (drop procedure, table exists, etc..)
  if ($TypeFolder -eq "StoredProcedure") {
  "IF OBJECT_ID('$ScriptFile', 'P') IS NOT NULL
   BEGIN
   DROP PROCEDURE $ScriptFile
   END
  GO
  " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
  } elseif ($TypeFolder -eq "View") {
  "IF OBJECT_ID('$ScriptFile', 'V') IS NOT NULL
   BEGIN
   DROP VIEW $ScriptFile
   END
  GO
  " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
  } elseif ($TypeFolder -eq "UserDefinedFunction") {
  "IF OBJECT_ID('$ScriptFile', 'FN') IS NOT NULL OR OBJECT_ID('$ScriptFile', 'TF') IS NOT NULL
   BEGIN
   DROP FUNCTION $ScriptFile
   END
  GO
  " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
  } elseif ($TypeFolder -eq "Trigger") {
  "IF OBJECT_ID('$ScriptFile', 'TR') IS NOT NULL
   BEGIN
   DROP TRIGGER $ScriptFile
   END
  GO
  " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
  } elseif ($TypeFolder -eq "Table") {
  "IF OBJECT_ID('$ScriptFile', 'U') IS NOT NULL
   BEGIN
   IF EXISTS(SELECT * FROM $ScriptFile)
    BEGIN
    USE tempdb
    RAISERROR('Table exists and contains data, cannot proceed.', 16, 1)
    RETURN
    END
   ELSE
    BEGIN
    DROP TABLE $ScriptFile
    END
   END
  GO
  " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
  }
  #Generate script for this object
  $scriptr.Script($ScriptThis)
  #Add new files to Perforce
  if (!($P4FileStatus))
   {
    p4 -u RealP4Login add -c $ChangeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL
    $ChangeListMessage = $ChangeListMessage + "Added " + $TypeFolder + " " + $ScriptFile + "
"
   }
  else
   {
    #"diff" file, revert if unchanged
    $P4FileDiff = p4 -u RealP4Login diff -sa $SavePath\$TypeFolder\$ScriptFile.SQL
    if (!($P4FileDiff))
     {
      echo "Reverting"
      p4 -u RealP4Login revert -c $ChangeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL
     }
    else
     {
      $ChangeListMessage = $ChangeListMessage + "Changed " + $TypeFolder + " " + $ScriptFile + "
"
     }
   }
 }
 #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 "\[|\]"
    #Check file status against Perforce
    $P4FileStatus = p4 -u RealP4Login fstat $SavePath\$TypeFolder\$ScriptFile.SQL
    if ($P4FileStatus)
     {
      #Open Perforce files for edit
      p4 -u RealP4Login sync $SavePath\$TypeFolder\$ScriptFile.SQL
      p4 -u RealP4Login edit -c $changeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL
     }
    #Set output filename for script
    $scriptr.Options.FileName = "$SavePath\Trigger\$ScriptFile.SQL"
    #Add commented USE statement for deployment validation tool
    "USE $dbname" >  "$SavePath\$TypeFolder\$ScriptFile.SQL"
    #Add existing object handler (drop trigger, etc..)
    "IF OBJECT_ID('$ScriptFile', 'TR') IS NOT NULL
     BEGIN
     DROP TRIGGER $ScriptFile
     END
    GO
    " >> "$SavePath\Trigger\$ScriptFile.SQL"
    #Generate script for this trigger
    $scriptr.Script($ScriptThis)
    #Add new files to Perforce
    if (!($P4FileStatus))
     {
      p4 -u RealP4Login add -c $ChangeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL
      $ChangeListMessage = $ChangeListMessage + "Added " + $TypeFolder + " " + $ScriptFile + "
"
     }
    else
     {
      #"diff" file, revert if unchanged
      $P4FileDiff = p4 -u RealP4Login diff -sa $SavePath\$TypeFolder\$ScriptFile.SQL
      if (!($P4FileDiff))
       {
        echo "Reverting"
        p4 -u RealP4Login revert -c $ChangeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL
       }
      else
       {
        $ChangeListMessage = $ChangeListMessage + "Changed " + $TypeFolder + " " + $ScriptFile + "
"
       }
     }
   }
  }
 #Check if changelist includes any new/modified files
 $P4FilesChanged = p4 -u RealP4Login opened -c $changeListNumber
 if ($P4FilesChanged)
  {
   #Submit changelist to Perforce
   p4 -u RealP4Login submit -c $changeListNumber
   #Send summary email
   $Now = Get-Date
   $SMTPserver = "RealSMTPServer"
   $from = "real@real-sql-guy.com"
   $to = "real@real-sql-guy.com"
   $subject = "$dbname database changes submitted to Perforce"
   $emailbody = "<h1>Changelist #$changeListNumber </h1>
<b>Submitted $Now </b>
<hr>$ChangeListMessage "
   $mailer = new-object Net.Mail.SMTPclient($SMTPserver)
   $msg = new-object Net.Mail.MailMessage($from, $to, $subject, $emailbody)
   $msg.IsBodyHTML = $true
   $mailer.send($msg)
  }
 else
  {
   #Delete changelist
   p4 -u RealP4Login change -d $changeListNumber
  }
 }
else
    {
        echo "P4 changelist could not be created"
    }