Printed 2016/02/11 02:21PM

A Month of PowerShell – Day 14 (Databases: Altering Tables)

By Wayne Sheffield, 2013/02/14

Welcome to Day 14 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at Please refer to this page to see all of the posts in this series, and to quickly go to them.

Adding columns and unique/default constraints

Yesterday we created a table with some columns in it. Continuing on with the mini-series on tables, today we’ll alter this table. We’ll add some new columns, a unique constraint, and add defaults to columns (some of the old and new columns).

Source code   
#Assign variables
$Instance   = "localhost\SQL2012"
$DBName     = "PoSh"
$SchemaName = "Common"
$TableName  = "TestTable"
#Assign the SMO class to a variable
$SMO        = "Microsoft.SqlServer.Management.Smo"
#Assign various data types to variables
$dtInt      = [Microsoft.SqlServer.Management.Smo.Datatype]::Int
$stSmallInt = [Microsoft.SqlServer.Management.Smo.Datatype]::SmallInt
$dtDateTime = [Microsoft.SqlServer.Management.Smo.Datatype]::DateTime
$dtSmallDt  = [Microsoft.SqlServer.Management.Smo.Datatype]::SmallDateTime
$dtDate     = [Microsoft.SqlServer.Management.Smo.Datatype]::Date
$dtTime     = [Microsoft.SqlServer.Management.Smo.Datatype]::Time
$dtBit      = [Microsoft.SqlServer.Management.Smo.Datatype]::Bit
$dtUniqueI  = [Microsoft.SqlServer.Management.Smo.Datatype]::UniqueIdentifier
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
# assign the database name to a variable
$MyDB = $Server.Databases[$DBName]
# assign the schema to a variable
$Schema = $MyDB.Schemas[$SchemaName]
# Check to see if the table exists
$Table = $MyDB.Tables.Item($TableName, $SchemaName)
IF (($Table))
    #Add a RowGUID (uniqueidentifier) column:
    # this will be the ROWGUIDCOL
    # it will have a default constraint
    # it will have a unique constraint
    $ObjectName = "RowGuid"
    $Column = $Table.Columns[$ObjectName]
    IF (!($Column))
        $Column = New-Object ("$SMO.Column") ($Table, $ObjectName, $dtUniqueI)
        $Column.RowGuidCol = $true
        $Column.DefaultConstraint.Text = 'NewSequentialID()'
    $ObjectName = "UQ_Rowquid"
    $IX = $Table.Indexes[$ObjectName]
    IF (!($IX))
        $IX = New-Object ("$SMO.Index") ($Table, $ObjectName)
        $IX.IndexKeyType = "DriUniqueKey"
        $IX.IsClustered = $false
        $IxCol = New-Object ("$SMO.IndexedColumn") ($IX, "RowGuid")
    #Add a bit column with a default constraint
    $ObjectName = "IsValid"
    $Column = $Table.Columns[$ObjectName]
    IF (!($Column))
        $Column = New-Object ("$SMO.Column") ($Table, $ObjectName, $dtBit)
        $Column.Nullable = $false
        $Column.DefaultConstraint.Text = '1'
    #Update the LastUpdatedDT column to have a default on it
    $Column = $Table.Columns['LastUpdatedDT']
    $Column.DefaultConstraint.Text = 'CURRENT_TIMESTAMP'
    #Update the LastUpdatedBy column to have a default on it
    $Column = $Table.Columns['LastUpdatedBy']
    $Column.DefaultConstraint.Text = 'SUSER_NAME()'
    #Finally, save these changes to the table
Get-ChildItem SQLSERVER:SQL\localhost\SQL2012\Databases\PoSh\Tables\Common.TestTable\Columns | `
    select-object ID, Name, DataType, RowGuidCol, Identity, IdentitySeed, IdentityIncrement, DefaultConstraint |`
    sort-object ID | `
    Format-Table -AutoSize

As this script shows, we added two new columns (RowGuid and IsValid), with default constraints on both of these columns. A unique constraint was added to the RowGuid column, and default constraints were added to two existing columns (LastUpdatedDT and LastUpdatedBy).

Copyright © 2002-2016 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.