Create Database Tables in PowerShell with SMO

As a rule, I believe that you should use the best tool for a given job. This belief goes back to the old saying "When all you have is a hammer, everything looks like a nail"—if you only have one tool in your belt, you tend to use that tool for everything. Generally, if I'm going to create a table, I'll use T-SQL Data Definition Language (DDL) statements to do so. However, there's value in understanding the SQL Server Management Objects (SMO) that are necessary to create a table. I'll use my belief in the value of understanding SMO as a base for future discussions of table objects in SMO and for explaining some of the interesting things you can do in SMO that aren't easily done in DDL. Understanding SMO will give you an extra tool on your belt for creating tables and executing other essential tasks.

Related: Using SQL Server Management Objects with PowerShell

Let's start by using SMO to create a table in AdventureWorks. As always, after loading the SQLPS module, we need to connect to the target SQL Server instance. Before we do so, we'll shorten our code a bit by creating a variable containing the namespace of the SMO library.

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

We also want to create some datatype objects we'll use later in the script; using variables makes the script later easier to read:

#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

Now, we can connect to the AdventureWorks database, where we'll create the table:

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

We'll create a table called AcquisitionCompany in the HumanResources schema. The Table object constructor has three arguments: the Database object ($db), the table name and the schema name, in that order:

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

Next, we'll create our primary key column, called CompanyID, which will have an int datatype and will be an IDENTITY column with a seed of 1 and an increment of 1 as well:

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

We can add the CompanyName column and the AcquisitionDate column and then set the columns to NOT NULL by setting the column's Nullable property to $False before adding the column:

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

Finally, we want to add the clustered index and declare that it is the primary key. We first create an index for the table, then set the IndexKeyType property to "DriPrimaryKey," where DRI stands for Declarative Referential Integrity, and we set the IsClustered property to $True. We need to define the CompanyID as the indexed column and add that column to the IndexedColumns collection for the index. Once we add the CompanyID column, we can add the index and then create the table:

#Create the Primary Key$idxpkcompany = new-object ("$ns.Index") ($tbcomp, "PK_AcquisitionCompany")$idxpkcompany.IndexKeyType = "DriPrimaryKey"$idxpkcompany.IsClustered = $true$idxpkcompanycol = new-object ("$ns.IndexedColumn") ($idxpkcompany, "CompanyID")$idxpkcompany.IndexedColumns.Add($idxpkcompanycol)$tbcomp.Indexes.Add($idxpkcompany) #Create the table$tbcomp.Create()

The table exists at this point, and its corresponding DDL looks like this:

CREATE TABLE [HumanResources].[AcquisitionCompany]([CompanyID] [int] IDENTITY(1,1) NOT NULL,[CompanyName] [nvarchar](100) NOT NULL,[AcquisitionDate] [datetime] NOT NULL, CONSTRAINT [PK_AcquisitionCompany] PRIMARY KEY CLUSTERED ([CompanyID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

Typically, I would use the DDL statements to create a table. However, there's value in understanding the SMO method for performing the same task, because it gives you a new tool and more options. By equipping yourself with multiple tools, you are better prepared to use the best tool for any job.

Related Articles: