Skip to content

Instantly share code, notes, and snippets.

@santiaago
Last active August 29, 2015 14:13
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save santiaago/2e907a8288c7dd15d0c3 to your computer and use it in GitHub Desktop.
Save santiaago/2e907a8288c7dd15d0c3 to your computer and use it in GitHub Desktop.
drift me
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

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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment