Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Month of PowerShell – Day 13 (Databases: Adding Schemas, Tables and Indexes)

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 -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:

$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.Name

This is also equivalent to:

$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 http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.datatype.aspx.

Comments

Leave a comment on the original post [blog.waynesheffield.com, opens in a new window]

Loading comments...