Add Schema, Add Table into a SQL Test Database

This is how you would create a schema and add a table into your own Test database.

Add-Type -Path 'C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll'

$ns = 'Microsoft.SqlServer.Management.Smo'
$svr = new-object ("$ns.Server") $inst

#Create reusable datatype objects
$dtint = [Microsoft.SqlServer.Management.Smo.Datatype]::Int
$dtvchar100 = [Microsoft.SqlServer.Management.Smo.Datatype]::NVarChar(100)
$dtdatetm = [Microsoft.SqlServer.Management.Smo.Datatype]::DateTime

#Reference the database.
$db = $svr.Databases["Test"]

#Schema variables
$SchemaName = "Inventory"
$Schema = $db.Schemas[$SchemaName]

#Ensure that the schema, if not, create it
if (!($Schema)) 
{
    Write-Host "Creating Schema: $SchemaName"
    $Schema = New-Object ('Microsoft.SqlServer.Management.SMO.Schema') ($db, $SchemaName)
    $Schema.Create()
}

#Create the table in the HumanResources schema
$tbcomp = new-object ("$ns.Table") ($db, "ComputerInfo", "Inventory")

#Create the ComputerID column
$colcoid = new-object ("$ns.Column") ($tbcomp, "ComputerID", $dtint)
$colcoid.Identity = $true
$colcoid.IdentitySeed = cls
$colcoid.IdentityIncrement = 1
$tbcomp.Columns.Add($colcoid)

#Create the ComputerName column
$colconame = new-object ("$ns.Column") ($tbcomp, "ComputerName", $dtvchar100)
$colconame.Nullable = $false
$tbcomp.Columns.Add($colconame)

#Create the AcquiredDate column
$colacqdate = new-object ("$ns.Column") ($tbcomp, "AcquiredDate", $dtdatetm)
$colacqdate.Nullable = $false
$tbcomp.Columns.Add($colacqdate)

#Create the Primary Key
$idxpkcomputer = new-object ("$ns.Index") ($tbcomp, "PK_AcquiredComputer")
$idxpkcomputer.IndexKeyType = "DriPrimaryKey"
$idxpkcomputer.IsClustered = $true
$idxpkcomputercol = new-object ("$ns.IndexedColumn") ($idxpkcomputer, "ComputerID")
$idxpkcomputer.IndexedColumns.Add($idxpkcomputercol)
$tbcomp.Indexes.Add($idxpkcomputer)
 
#Create the table
$tbcomp.Create()