EN_to_TFS_1 - a powershell script that will, based on parameters, GET/CHECKOUT objects from TFS, overwrite them with the version from the server scripted via SMO, then ADD/CHECKIN the code. Provide just servername/databasename to script every object in a database.

sql_script_to_TFS.ps1

<#
.SYNOPSIS
  Will script an object from SQL Server and CHECKIN/ADD to TFS.
.EXAMPLE 
  sql_script_to_TFS.ps1 -server yourservername -Database yourdatabasname -ScriptType "FULL" -Author yourTFSname -Comment "full checkin of database"

#>
# Purpose - given parameters, script out an object from a SQL Server, using SMO, then check it into TFS.
#Scripting code gleefully copied from Phil Factor - the great parts that work are his, the errors are mine
# https://www.simple-talk.com/sql/database-administration/automated-script-generation-with-powershell-and-smo/
#Other code from others copied and attributed below.

#mdb 2013/12/03 adding ScriptType so that we can add "create database"-specific changes ONLY. 
#     To do FULLs you will have to have  -ScriptType = 'Full' parameter. By default it will do objects.
#mdb 2013/12/05 trying to better bullet-proof the app against TF failures, which appear to occur randomly
#mdb 2013/12/07 works great, though occasionally it cant script out an item.  Trying to bulletproof that
#mdb 2013/12/16 broke the individual-item portion, fixed. 	  
#mdb 2013/12/17 more cleanup and dealing with no objects found.
#mdb 2014/09/08 removing .exe references, and changing folder name, so 2013 works.
#mdb 2018/03/06 the -notmatch doesn't include usp_M* under certain circumstances. Changing to notlike

#param has to be the first line of the script.
param( 
        [Parameter(Mandatory=$true,Position=1)]
         [string]$Server, 
        [Parameter(Mandatory=$true,Position=2)]
         [string]$Database, 
        [string]$ScriptType ='Object',
        [string]$SchemaToScript, 
        [string]$ObjectToScript, 
        [string]$Author, 
        [string]$Comment
      )
        #make sure not ObjectToScript blank if scripting out the entire DB; makes checkin better

#these are the parameters for testing
#$Server='sql_repository'# the server it is on
#$Database='model' # the name of the database you want to script as objects
#$SchemaToScript = 'dbo'
#$ObjectToScript = 'spy_tempdef'
#$Author = 'michael.bourgon'
#$ScriptType ='Object'
#$Comment = 'bourgon_test'

#setting up an error code for later
$myerror = 0

cd c:\tfs_cli\en_workspace

if ($comment -eq '')
{ $comment = "generic EN checkin"}

if ($author -eq '')
{ $author = "erxnetwork\sqlservice"} 
#field is mandatory, if we dont know, use a known-valid.

$ServerNameClean = "$($Server -replace '[\\\/]','__')"
clear 

#writing this out for logging and troubleshooting. These are all the parameters except ScriptType
write-host $Server, $Database, $SchemaToScript, $ObjectToScript, $Author, $Comment

#TFS workspace folder - whatever you set it up as on your server
$DirectoryToSaveTo='C:\TFS_CLI\EN_Workspace' # the directory where you want to store them

# Load SMO assembly, and if we are running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
}
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoEnum') | out-null
set-psdebug -strict # catch a few extra bugs
$ErrorActionPreference = "stop"
$My='Microsoft.SqlServer.Management.Smo'
$srv = new-object ("$My.Server") $Server # attach to the server
if ($srv.ServerType-eq $null) # if it managed to find a server
   {
   Write-Error "Sorry, but I could not find Server '$Server' "
   return
}

$scripter = new-object ("$My.Scripter") $srv # create the scripter
#Add the various options we care about
$scripter.Options.ToFileOnly = $true   
$scripter.Options.ExtendedProperties= $true # yes, we want these
$scripter.Options.DRIAll= $true # and all the constraints
$scripter.Options.Indexes= $true # Yup, these would be nice
$scripter.Options.Triggers= $true # This should be includede
$scripter.Options.AppendToFile = $False
$scripter.Options.AllowSystemObjects = $False
$scripter.Options.ClusteredIndexes = $True
$scripter.Options.DriAll = $True
$scripter.Options.ScriptDrops = $False
$scripter.Options.IncludeHeaders = $False #so you do not get the one line "scripted at..." which would be NOW.
#$scripter.Options.ToFileOnly = $True
#$scripter.Options.Indexes = $True
$scripter.Options.Permissions = $True
$scripter.Options.WithDependencies = $False
$scripter.Options.Bindings = $true
$scripter.Options.IncludeDatabaseRoleMemberships = $true


#################################################
#First, script out the database "create" itself.# 
#################################################
if (($Database) -and $ObjectToScript -eq '')  #if database has a value but there is no object, script out the DB.
{
$db_scripter = new-object ("$My.Scripter") $srv # script out the database creation
$db_scripter.options=$scripter.options # with the same options
$db_scripter.options.filename="$($DirectoryToSaveTo)\$($ServerNameClean)\$($Database)\$($Database)_database_create.sql" # with the same options

#explcitly creating the path for the DB script here. We still need to do it for all the sub-types.
# Could probably move the $d declaration up here, but leaving it here for readability.
$SavePath="$($DirectoryToSaveTo)\$($ServerNameClean)\$($Database)"
# create the directory if necessary (SMO does not).
if (!( Test-Path -path $SavePath )) # create it if not existing
     {Try { New-Item $SavePath -type directory | out-null }
     Catch [system.exception]{
         Write-Error "error while creating '$SavePath' $_"
         return
      }
 }
#Use TF to see if the object exists on our TFS server. 
# Optimization idea: DIR the entire subfolder on a FULL and compare all at once.
$tf = &cmd /c "c:\TFS_CLI\App_2013\TF dir $/Randolph/$ServerNameClean/$Database/$($Database)_database_create.sql 2>&1"
# Running the TF calls this way as per Simon Ejsing to ignore the error state and capture the actual error message
#     http://stackoverflow.com/questions/2095088/error-when-calling-3rd-party-executable-from-powershell-when-using-an-ide
# However, that also means we need 

#Note that if the database create has not changed, it will still attempt to CHECKIN, but TFS will ignore as it is the same.
if ($tf -like "No items match*" -or $tf -like "*is not found or not supported*")
    {
        "database script does not exist; scripting out and ADDing to TFS"

        if(Test-Path -Path $db_scripter.options.filename)
            {
            #delete the file manually, since we have seen permission issues where the $script cannot overwrite.
            $deleteme = "$SavePath\$($Database)_database_create.sql" 
            $deleteme
            try
                {
                remove-item "$SavePath\$($Database)_database_create.sql" -force
                }
            catch
                {
                $error[0].Exception
                }        
            }

        #putting in a try/catch so we get error messages if it breaks, and it can continue.
        try
            {
            $db_scripter.Script($srv.Databases[$Database]) # do it        
            }
        Catch
            {  
            "Error Message trying to script out $SavePath\$Filename"
            $error[0].Exception
            }

        "database create script done"
        $tf = &cmd /c "c:\TFS_CLI\App_2013\TF add $/Randolph/$ServerNameClean/$Database/$($Database)_database_create.sql 2>&1"
        $tf
        #use mass checkin at the end
    }
else
    {
        "database script exists; get, check out, script to override, check in"
        $tf = &cmd /c "c:\TFS_CLI\App_2013\TF get ""$/Randolph/$ServerNameClean/$Database/$($Database)_database_create.sql"" /noprompt 2>&1"
        "database script GET results"
        $tf
        $tf = &cmd /c "c:\TFS_CLI\App_2013\TF checkout ""$/Randolph/$ServerNameClean/$Database/$($Database)_database_create.sql"" 2>&1"
        "database script CHECKOUT results"
        $tf
        "database checkout done"

        #If the file exists, manually delete; we have seen permission issues where $script cannot overwrite.
        if(Test-Path -Path $db_scripter.options.filename)
            {
            $deleteme = "$SavePath\$($Database)_database_create.sql" 
            $deleteme
            try
                {
                #bug exists with standard remove - if there are read-only items in the same folder, -force is required
                remove-item "$SavePath\$($Database)_database_create.sql" -force
                }
            catch
                {
                $error[0].Exception
                }        
            }
                    
        #putting in a try/catch so we get error messages if it breaks, and it can continue.
        try
        {
        $db_scripter.Script($srv.Databases[$Database]) # do it        
        }
        Catch
        {  
        "Error Message trying to script out $SavePath\$Filename"
        $error[0].Exception
        }        
        "database script out done"
        #use mass checkin at the end
    }
}


###########################
## Scripting out Objects ##
###########################
# we now get all the object types except extended stored procedures

# first we get the bitmap of all the object types we want
$all =[long] [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::all `
    -bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ExtendedStoredProcedure
# and we store them in a datatable
$d = new-object System.Data.Datatable

# get almost everything; skipping most service broker, information_schema, system_views, certificates (cannot be scripted)
#  there are other items that may need to be skipped, like SymmetricKeys in SSISDB
# Yes, I realize the irony in skipping SB given that it is powering this.  

        #putting in a try/catch so we get error messages if it breaks, and it can continue.
        try
            {
            $d=$srv.databases[$Database].EnumObjects([long]0x1FFFFFFF -band $all) | `
                Where-Object {$_.Schema -ne 'sys'-and $_.Schema -ne "information_schema" -and $_.DatabaseObjectTypes -ne 'ServiceBroker' `
                -and $_.DatabaseObjectTypes -ne 'Certificate' `
                -and $_.DatabaseObjectTypes -ne 'MessageType' `
                -and $_.DatabaseObjectTypes -ne 'ServiceContract' `
                -and $_.DatabaseObjectTypes -ne 'ServiceQueue' `
                -and $_.DatabaseObjectTypes -ne 'ServiceRoute' `
                -and ($SchemaToScript -eq '' -or $_.Schema -eq $SchemaToScript) `
                -and (($ObjectToScript -eq '' -and $ScriptType -eq 'Full') -or $_.Name -eq $ObjectToScript) `
                -and ($_.Name -notlike 'sp_MS*') }
            # mdb 2013/11/07 previous line skips replication objects.  This comment below code as comment lines break extended 1-liner.
            }
        Catch
            {  
            "Error Message trying to enumerate the database - may be logshipped or being restored"
            $myerror = 1
            $error[0].Exception
            }


# List every item that we are going to do
$d = $d | sort -Property DatabaseObjectTypes,Schema,Name
$d | select databaseobjecttypes, schema, name
 
if ($d.Count -gt 10000)
{
"skipping the database objects - more than 10000"
}

# Now write out each scriptable object as a file in the directory you specify
#it appears that an empty array never actually enters the FOREACH, leaving variables unset
# -and -$d.Count -ne 0
if ($myerror -eq 0 -and $d.Count -lt 10001) #20k of objects takes up 5gb of RAM in the PS script and causes problems
{
$d| FOREACH-OBJECT { # for every object we have in the datatable.
   "" #blank line so each block of error messages is separated out
   $SavePath="$($DirectoryToSaveTo)\$ServerNameClean\$Database\$($_.DatabaseObjectTypes)"

   # create the directory if necessary (SMO does not).
   if (!( Test-Path -path $SavePath )) # create it if not existing
        {Try { New-Item $SavePath -type directory | out-null }
        Catch [system.exception]{
            Write-Error "error while creating '$SavePath' $_"
            return
         }
    }
    # tell the scripter object where to write it, and make sure it is actually writeable
    if ($_.schema)
    {
    $Filename = "$($_.schema -replace '[\\\/\:\.]','-').$($_.name -replace '[\\\/\:\.\ ]','-').sql";
    }
    else
    {
    $Filename = "$($_.name -replace '[\\\/\:\.]','-').sql";
    }

    $scripter.Options.FileName = "$SavePath\$Filename"
    $scripter.Options.FileName #print it out so we know what is being done

    $UrnCollection = new-object ('Microsoft.SqlServer.Management.Smo.urnCollection')
    $URNCollection.add($_.urn)


############################
# TFS code for each object #
############################
#Use TF to see if the object exists on our TFS server
"checking to see if object exists"
$tf = &cmd /c "c:\TFS_CLI\App_2013\TF dir $/Randolph/$ServerNameClean/$Database/$($_.DatabaseObjectTypes)/$Filename 2>&1"
# Running all the TF commands this way as per Simon Ejsing to ignore the error state and capture the actual error message.
#     http://stackoverflow.com/questions/2095088/error-when-calling-3rd-party-executable-from-powershell-when-using-an-ide

if ($tf -like "No items match*" -or $tf -like "*is not found or not supported*")
{
    "no items match; scripting out and ADDing to TFS"

    try
    {
    $scripter.script($URNCollection)
    }
    Catch
    {  
    "Error Message trying to script out $SavePath\$Filename"
    $error[0].Exception
    }

    "script done"

    $tf = &cmd /c "c:\TFS_CLI\App_2013\TF add  /noprompt ""$/Randolph/$ServerNameClean/$Database/$($_.DatabaseObjectTypes)/$Filename"" 2>&1"
    $tf

    #mdb 2013/11/07 only do ONE checkin at the end if we are doing an entire database; all will have the same comment
    if ($ObjectToScript -ne '')
    {
    $tf = &cmd /c "c:\TFS_CLI\App_2013\TF checkin /author:$author /comment:""$comment"" /noprompt 2>&1"
    $tf
    }
}
else
{
    "item exists; get, check out, script to override, check in"
    #noprompt causes it to crash, virtually every time
    $tf = &cmd /c "c:\TFS_CLI\App_2013\TF get ""$/Randolph/$ServerNameClean/$Database/$($_.DatabaseObjectTypes)/$Filename"" 2>&1"
    $tf
    $tf = &cmd /c "c:\TFS_CLI\App_2013\TF checkout ""$/Randolph/$ServerNameClean/$Database/$($_.DatabaseObjectTypes)/$Filename"" 2>&1"
    $tf

    #Delete file before scripting; we have seen permission issues.  
    if(Test-Path -Path $scripter.options.filename)
    {
    try
        {
        remove-item "$SavePath\$Filename" -force
        }
    catch
        {
        $error[0].Exception
        }        
    }

    try
    {
    $scripter.script($URNCollection)
    }
    Catch
    {  
    "Error Message trying to script out $SavePath\$Filename"
    $error[0].Exception
    }

    #mdb 2013/12/03 making this part only run if it is a specific object; that way we can rerun an entire database
    if ($ObjectToScript -ne '')
    {
    $tf = &cmd /c "c:\TFS_CLI\App_2013\TF checkin /author:$author /noprompt /comment:""$comment"" ""$/Randolph/$ServerNameClean/$Database/$($_.DatabaseObjectTypes)/$Filename"" 2>&1"
    $tf
    }
}


}
}

#If it is a mass add or a database-specific, CHECKIN now.
if ($ObjectToScript -eq '')
{
    "final mass checkin"
    $tf = &cmd /c "c:\TFS_CLI\App_2013\TF checkin /author:$author /comment:""$comment"" /noprompt 2>&1"
    #$tf we do not need this one here because it will be shown below
    #$tf
    "mass checkin done"
}
"--------------------------------------------------------------------------"
#if the checkin failed, UNDO so the next one does not make it worse.  
#The next checkin would probably fix it, but I have had to go back and manually undo. Not fun.
#mdb 20131107 If there were any items to check in, get the results.  Throws an error otherwise.

#using Stej code to verify the variable exists; if no objects, $tf is never set, so it bombs here
#  http://stackoverflow.com/questions/3159949/in-powershell-how-do-i-test-whether-or-not-a-specific-variable-exists-in-global

if (Test-Path variable:local:tf)
{
if ($tf -like "Changeset * checked in." -or $tf -like "There are no remaining changes to check in.")
{
    #mdb 20131107 If there were any items to check in, get the results.  Throws an error otherwise.
    if ((Test-Path variable:local:d)  -and $ObjectToScript -eq '')
    {
    $tf
    }
}
else
{
   "changes not made - errorlog follows"
   $tf
   "================================UNDO BEGINS=================================="
   $tf = &cmd /c "c:\TFS_CLI\App_2013\TF undo ""$/Randolph/$ServerNameClean/$Database/*.*"" /recursive 2>&1"
   $tf
   "=================================UNDO ENDS==================================="
}
}
else 
{
"No objects found, nothing done"
}

#TFS rollback code, should look something like this
#c:\TFS_CLI\App_2013\TF undo $/Randolph/$ServerNameClean/VOAgent/DatabaseRole/*.* 
#No files checked in due to conflicting changes. These conflicting changes have been automatically resolved. Please try the check-in again.

#rolling back code: c:\TFS_CLI\App_2013\TF undo $/Randolph/cm-01/VOAgent/DatabaseRole/*.* 
#for some reason "override:" did not work.

#C:\TFS_CLI\EN_Workspace\server\file.sql
#item exists; get, check out, script to override, check in
#All files are up to date.