drift me

driftme.ps1

param([String]$server="yourservername", 
        [String]$sqlServerVersion="sql2014", 
        [String]$dbToDrift="yourdbtodrift", 
        [String]$tableToDrift="yourtabletodrift")

$previousLocation = Get-Location
Import-Module "sqlps" -DisableNameChecking

SQLSERVER:
$directory = "\sql\{0}\{1}\databases\{2}" -f $server, $sqlServerVersion, $dbToDrift
""
"Drift Me!"
""
"Running in {0}" -f $directory
"This script will perform a drift on database: {0} in table: {1}" -f $dbToDrift, $tableToDrift

try {
    Set-Location $directory  -ErrorAction Stop
} catch{
    ""
    "--> Oops, something went wrong!"
    $_.Exception.Message
    Set-Location $previousLocation
    break
}

## search for a column starting with name driftme-
$oldColName = ""
$hasDriftColumn = $false
$lookForDriftCol = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}' AND TABLE_SCHEMA='dbo' AND COLUMN_NAME like 'driftme-{0}-%'" -f $tableToDrift
try{
    $result = invoke-sqlcmd -query $lookForDriftCol -ErrorAction Stop

    if( $result.COLUMN_NAME -ne $NULL){
        $hasDriftColumn = $true
        $oldColName = $result.COLUMN_NAME
    }
} catch{
    ""
    "--> Oops, something went wrong when trying to search for existing driftme column!"
    ""
    $_.Exception.Message
    Set-Location $previousLocation
    break
}

$newColName = ""
## rename or create column
if($hasDriftColumn -eq $True){
    $newColNameForRename = "driftme-{0}-{1}" -f $tableToDrift, [guid]::NewGuid()
    $renameQuery = "EXEC sp_RENAME '{0}.{1}', '{2}', 'COLUMN'" -f $tableToDrift, $oldColName, $newColNameForRename
    try{
        invoke-sqlcmd -query $renameQuery -ErrorAction Stop
        $newColName = $newColNameForRename
    } Catch{
     ""
        "--> Oops, something went wrong!"
        ""
        "Error running drift query '{0}'" -f $renameQuery    
        $_.Exception.Message
        break
    }
} else{
    $newColNameForAlter = "[driftme-{0}-{1}]" -f $tableToDrift, [guid]::NewGuid()
    $alterquery = "alter table {0} add {1} nchar(10) null" -f $tableToDrift, $newColNameForAlter
    try{
        invoke-sqlcmd -query $alterquery -ErrorAction Stop
        $newColName = $newColNameForAlter
    } Catch{
        ""
        "--> Oops, something went wrong!"
        ""
        "Error running drift query '{0}'" -f $alterquery    
        $_.Exception.Message
        break
    }
}

if($hasDriftColumn){
    "Drift successfull"
    "On table {0}.dbo.{1}" -f $dbToDrift, $tableToDrift
    "Renamed column '{0}'" -f $oldColName
    "To '{0}'" -f $newColName
}else{
    "Drift successfull"
    "On table {0}.dbo.{1} added column '{2}'" -f $dbToDrift, $tableToDrift, $newColName 
}
Set-Location $previousLocation

gistfile1.md

driftme script:

driftme.ps1 lets you drift a database by creating a column with the pattern driftme-tablename-GUID on a specific table. If the column already exists it renames it by generating a new GUID

default values:

The script comes with some default values that you might want to change:

$server="yourservername"
$sqlServerVersion="sql2014"
$dbToDrift="yourdbtodrift"
$tableToDrift="yourtabletodrift"

Variables:

This is the list of variables that you can override:

• -server : the server where the database is.
• -sqlversion : the version of the sql server (sql2012 or sql2014).
• -dbToDrift: the database to drift.
• -tableToDrift: the table to drift.

Drift with default values:

PS C:\> C:\driftme.ps1

Drift Me!

Running in \sql\yourservername\sql2014\databases\yourdbtodrift
This script will perform a drift on database: yourdbtodrift in table: yourtabletodrift

WARNING: Using provider context. Server = yourservername\sql2014, Database = yourdbtodrift.
WARNING: Using provider context. Server = yourservername\sql2014, Database = yourdbtodrift.


Drift successfull
On table yourdbtodrift.dbo.yourtabletodrift added column '[driftme-yourtabletodrift-95c59155-4c48-49d8-a298-7cbe2d1ca953]'

If the drift column exist it renames it again

Drift successfull
On table yourdbtodrift.dbo.yourtabletodrift
Renamed column 'driftme-yourtabletodrift-95c59155-4c48-49d8-a298-7cbe
To 'driftme-yourtabletodrift-f4ba6538-b978-4ad9-897c-c111da300bed'

Drift by changing dbToDrift:

PS C:\> C:\driftme.ps1 -dbtodrift "anotherdb"

Drift by changing multiple params:

PS C:\> C:\driftme.ps1 -tabletodrift "anothertable" -dbtodrift "anotherdb"