SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

Welcome to Day 14 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at http://blog.waynesheffield.com/wayne/a-month-of-powershell/. 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).

#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).


Leave a comment on the original post [blog.waynesheffield.com, opens in a new window]

Loading comments...