Welcome to Day 13 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.
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.
#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.
#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 -AutoSizeMost of the SMO classes have different overloaded constructors that allow for various parameters being supplied. For instance, this line from the above script:
$Table = New-Object ("$SMO.Table") ($MyDB, $TableName, $Schema.Name)This is equivalent to:
$Table = New-Object ("$SMO.Table")
$Table.Parent = $MyDB
$Table.Name = $TableName
$Table.Schema = $Schema.NameThis is also equivalent to:
$Table = New-Object ("$SMO.Table") ($MyDB, $TableName)
$Table.Schema = $Schema.NameNote 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 http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.datatype.aspx.
