Welcome to Day 13 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.
We have created a database named PoSh to play around with. But what use is an empty database? So, we’re going to start adding different objects. Today, we’re starting a mini-series of working with tables, where the next several days will be about tables.
Schemas
Unless you’re planning on putting everything into the default dbo schema, you’ll need to add a schema. Let’s start off by adding one named Common.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
#Assign variables $Instance = ".\SQL2012" $DBName = "PoSh" $SchemaName = "Common" # get the server $Server = New-Object ('Microsoft.SqlServer.Management.SMO.Server') "$Instance" # assign the database name to a variable $MyDB = $server.Databases[$DBName] $Schema = $MyDB.Schemas[$SchemaName] if (!($Schema)) #Ensure that the schema doesn't exist before creating it { Write-Host "Creating Schema: $SchemaName" $Schema = New-Object ('Microsoft.SqlServer.Management.SMO.Schema') ($MyDB, $SchemaName) $Schema.Create() } |
Tables
Now that we have a schema, it’s time to create a table. In this example, we’ll add to the above script to create a table in the “Common” schema named “TestTable”, with a few columns including an identity column and a clustered primary key on this column.
|
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 |
#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 # 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] if (!($Schema)) #Ensure that the schema doesn't exist before creating it { Write-Host "Creating Schema: $SchemaName" $Schema = New-Object ("$SMO.Schema") ($MyDB, $SchemaName) $Schema.Create() } # Check to see if the table exists $Table = $MyDB.Tables.Item($TableName, $SchemaName) IF (!($Table)) { Write-Host "Creating Table: [$SchemaName].[$TableName]" # Get a new table object $Table = New-Object ("$SMO.Table") ($MyDB, $TableName, $Schema.Name) # Get a new identity column object of the integer data type, named the name of the table + "ID" $Column = New-Object ("$SMO.Column") ($Table, "$($TableName)ID", $dtInt) $Column.Identity = $true $Column.IdentitySeed = 1 $Column.IdentityIncrement = 1 # Add this column to the table $Table.Columns.Add($Column) $Column = New-Object ("$SMO.Column") ($Table, "LastUpdatedDT", $dtDateTime) $Table.Columns.Add($Column) $Column = New-Object ("$SMO.Column") ($Table, "LastUpdatedBy", [Microsoft.SqlServer.Management.SMO.DataType]::NVARCHAR(100)) $Table.Columns.Add($Column) # Create the clustered primary key on the identity column $IX = New-Object ("$SMO.Index") ($Table, "PK_$TableName") $IX.IndexKeyType = "DriPrimaryKey" $IX.IsClustered = $true # Add this column to the index $IX_col = New-Object ("$SMO.IndexedColumn") ($IX, "$($TableName)ID") $IX.IndexedColumns.Add($IX_col) # Add the index to the table $Table.Indexes.Add($IX) # Create the table $Table.Create() } $MyDB.Tables | Select-Object Parent, Schema, Name | Format-Table -AutoSize |
Most of the SMO classes have different overloaded constructors that allow for various parameters being supplied. For instance, this line from the above script:
|
1 |
$Table = New-Object ("$SMO.Table") ($MyDB, $TableName, $Schema.Name) |
This is equivalent to:
|
1 2 3 4 |
$Table = New-Object ("$SMO.Table") $Table.Parent = $MyDB $Table.Name = $TableName $Table.Schema = $Schema.Name |
This is also equivalent to:
|
1 2 |
$Table = New-Object ("$SMO.Table") ($MyDB, $TableName) $Table.Schema = $Schema.Name |
Note the data type designation for the columns in this table. The data type needs to come from the Microsoft.SqlServer.Management.Smo.Datatypes class. As demonstrated when creating a new column object, they can be put into a variable to use the variable, or you can specify the class. A complete list of available data types for use is available at //msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.datatype.aspx.
