#Assign variables
$Instance   = "localhost\SQL2012"
$DBName     = "PoSh"
$SchemaName = "Common"
$TableName  = "TestTable2"
 
#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))
{
    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
    $IX_col = New-Object ("$SMO.IndexedColumn") ($IX, "$($TableName)ID")
    $IX.IndexedColumns.Add($IX_col)
    $Table.Indexes.Add($IX)
 
    #Add a RowGUID (uniqueidentifier) column:
    $ObjectName = "RowGuid"
    $Column = $Table.Columns[$ObjectName]
    IF (!($Column))
    {
        $Column = New-Object ("$SMO.Column") ($Table, $ObjectName, $dtUniqueI)
        $Table.Columns.Add($Column)
    }
 
    $ObjectName = "IX_Rowquid"
    $IX = $Table.Indexes[$ObjectName]
    IF (!($IX))
    {
        $IX = New-Object ("$SMO.Index") ($Table, $ObjectName)
        $IX.IndexType = "NonClusteredIndex"
        $IX.IndexKeyType = "None"
        $IX.IsClustered = $false
        $IxCol = New-Object ("$SMO.IndexedColumn") ($IX, "RowGuid")
        $IX.IndexedColumns.Add($IxCol)
        $Table.Indexes.Add($IX)
    }
 
    # Create the table
    $Table.Create()
 
    #build a foreign key to Common.TestTable based on the RowGuid column
    $ObjectName = "FK_$($TableName)"
    $FK = New-Object ("$SMO.ForeignKey") ($Table, $ObjectName)
    #Perform the next two statements for each column in the FK
    $FKCol = New-Object ("$SMO.ForeignKeyColumn") ($FK, "RowGuid", "RowGuid")
    $FK.Columns.Add($FKCol)
    $FK.ReferencedTable = "TestTable"
    $FK.ReferencedTableSchema = "Common"
    $FK.Create()
 
}
 
cd SQLSERVER:\SQL\localhost\SQL2012\Databases\PoSh\Tables\Common.TestTable2
Get-ChildItem Columns | `
    select-object ID, Name, DataType, RowGuidCol, Identity, IdentitySeed, IdentityIncrement, DefaultConstraint |`
    sort-object ID | `
    Format-Table -AutoSize
 
Get-ChildItem ForeignKeys