In an earlier discussion, we walked through the process of creating a table to the AdventureWorks database. While it's better to create a table using data definition language (DDL), there are some things that are better done using SQL Server Management Objects (SMO). In this example, we're asked to add columns to all the tables in a database to store values showing the last time a row was modified, and who modified it.
Related: SQL Server and PowerShell FAQs
The script will have command-line arguments for instance ($inst) and database name ($database). After connecting to the instance and getting the Database object, the script will cycle through the tables in the database, add two columns called ModifiedBy and ModifiedDTM, to each table. The columns will have default values of the name of the current logged in user for ModifiedBy and the current date/time for ModifiedDTM.
First, we'll connect to the instance and to the database.
# Connect to the specified instance
$ns = 'Microsoft.SqlServer.Management.Smo'
$svr = new-object ("$ns.Server") $inst
# Get the specified database where the changes will be made
$db = $svr.Databases[$database]
We also need to create reusable data types like we did when we created a table.
#Create reusable datatype objects $dtvchar30 = [Microsoft.SqlServer.Management.Smo.Datatype]::NVarChar(30) $dtdatetm = [Microsoft.SqlServer.Management.Smo.Datatype]::DateTime
Next, we'll cycle through each of the tables in the database and if the column doesn't already exist, we'll create it. After defining the basic characteristics of each column, we'll add a constraint, and then set the text of the default constraint to the code that'll populate that column when rows are added or updated.
Note that the AddDefaultConstraint method used for each column has the table name embedded in the name (to keep them unique), but because there aren't natural delimiters on either side of the $tn variable containing the table name, we embed that variable inside parentheses preceded by the dollar sign—this forces PowerShell to evaluate the variable and allows it to substitute the name in its proper place. We also pipe the output of that method to out-null, because if we don't, the method will send the new name to the console, and we don't need it.
foreach ($tb in $db.Tables) {
$tn = $tb.Name
$col = $tb.Columns['ModifiedBy']
if ($col.Count -eq 0) {
#Create the ModifiedBy column
$colmb = new-object ("$ns.Column") ($tb, "ModifiedBy", $dtvchar30 )
$colmb.Nullable = $true
$colmb.AddDefaultConstraint("Def_$($tn)_ModifiedBy") | out-null
$colmb.DefaultConstraint.Text = 'SUSER_NAME()'
$tb.Columns.Add($colmb)
}
$col = $tb.Columns['ModifiedDTM']
if ($col.Count -eq 0) {
#Create the ModifiedDTM column
$colmd = new-object ("$ns.Column") ($tb, "ModifiedDTM", $dtdatetm )
$colmd.Nullable = $true
$colmd.AddDefaultConstraint("Def_$($tn)_ModifiedDTM") | out-null
$colmd.DefaultConstraint.Text = 'CURRENT_TIMESTAMP'
$tb.Columns.Add($colmd)
}
# Now Alter the table to actually make the change
$tb.Alter()
}
Once this script is run against a database, all tables in that database will have the ModifiedBy and ModifiedDTM columns added, with proper default values.
Related: 4 Essential PowerShell Provider Commands for SQL Server
