Add New Table into AdventureWorks2016 Database

email me

This is how you would add a new table into a SQL database using PowerShell.

The idea is, if HR was to assign an employee a laptop, and then upon first login, the user info would be recorded into the database. You could extend this to record IP address, computer serial number, MAC addresses, etc.

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 AdventureWorks database.
$db = $svr.Databases["AdventureWorks2016"]

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

#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()