Welcome to Day 14 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at //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).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
#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).
