http://www.sqlservercentral.com/blogs/discussionofsqlserver/2013/02/14/a-month-of-powershell-day-14-databases-altering-tables/

Printed 2014/04/20 10:50AM

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

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.AddDefaultConstraint("DF_$($Column.Name)")
        $Column.DefaultConstraint.Text = 'NewSequentialID()'
        $Table.Columns.Add($Column)
    }
 
    $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")
        $IX.IndexedColumns.Add($IxCol)
        $Table.Indexes.Add($IX)
    }
 
    #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.AddDefaultConstraint("DF_$($Column.Name)")
        $Column.DefaultConstraint.Text = '1'
        $Table.Columns.Add($Column)
    }
 
    #Update the LastUpdatedDT column to have a default on it
    $Column = $Table.Columns['LastUpdatedDT']
    $Column.AddDefaultConstraint("DF_$($Column.Name)")
    $Column.DefaultConstraint.Text = 'CURRENT_TIMESTAMP'
    $Column.Alter()
 
    #Update the LastUpdatedBy column to have a default on it
    $Column = $Table.Columns['LastUpdatedBy']
    $Column.AddDefaultConstraint("DF_$($Column.Name)")
    $Column.DefaultConstraint.Text = 'SUSER_NAME()'
    $Column.Alter()
 
    #Finally, save these changes to the table
    $Table.Alter()
}
 
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-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.