#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