Creating a table using Power Shell and SQL Server Management Objects

Review this previous post    http://hodentekmsss.blogspot.com/2014/11/accessing--
sql-server-via-smo-using.html

In the above post you will get an introduction to Power Shell and SQL Server
Management Objects. You also learn how to access the SQL Server and carry out a
few tasks like listing out all the databases.

Review this post for creating a database using Power Shell and SQL Server
Management Objects:
http://hodentekmsss.blogspot.com/2015/02/creating-database-using-power-shell-
and.html

In the above post you will learn how to create a new database in SQL Server using
SMO and Power Shell. Using Transact-SQL or the SSMS you can easily create a
database. Using PowerShell is another option to create a database using a script.

Having created a database you would want to create tables in the database.

This post shows how you may create a table. This script has been tested to work with
Power Shell ver. 4.0.

Launch SQLPS from Start | Run and type-in SQLPS and click OK.

PS_SMOTable01.png

Let us get connected to the instance of SQL Server (RegencyPark) on this computer
(Hodentek8) by (declaring) running the command shown.

PS SQLSERVER:\>  $server= new-object Microsoft.Sqlserver.management.smo.server
'Hodentek8\RegencyPark'

Let us create a database named 'Feb6'.
Declare a name for the database as shown here:
PS SQLSERVER:\SQL\HODENTEK8\REGENCYPARK> $dbname= "Feb6"

Now create a database object variable in the instance using SMO:
PS SQLSERVER:\> $db=new-object  Microsoft.Sqlserver.management.smo.Database
($server,$dbname)

Now create the database using the create () function.
PS SQLSERVER:\>  $db.Create()
When you run the above command you will have created a database called Feb6 in the
SQL Server instance as shown (this may take a few seconds and you will not get any response except if there are errors) in SQL Server Management Studio:

PS_SMOTable02.png

In creating the table run the following lines of command in PS SQLSERVER:\>:

We declare a name for the table as 'SmoCustomers' and instantiate the table object
by running the following:
$tbname = "SmoCustomers"
$tb = new-object  Microsoft.Sqlserver.management.smo.Table ($db, $tbname)

We assume the table to have three columns (fields) named 'Field1, Field2 and
Field3. Field1 is of int data type and the two others are nvarchar(num) data type.

Typically instantiate a column object and its data type and add that column to the
$tb.Columns. Carry out this for the three columns by running these lines in PS
SQLSERVER:\>.

$colField1 = New-Object Microsoft.SqlServer.Management.Smo.Column ($tb, "Field1")
 $colField1.DataType = [Microsoft.SqlServer.Management.Smo.Datatype]::Int
 $tb.Columns.Add($colField1)

$colField2 = New-Object Microsoft.SqlServer.Management.Smo.Column ($tb, "Field2")
 $colField2.DataType = [Microsoft.SqlServer.Management.Smo.Datatype]::NVarchar(25)
 $tb.Columns.Add($colField2)

$colField3 = New-Object Microsoft.SqlServer.Management.Smo.Column ($tb, "Field3")
$colField3.DataType = [Microsoft.SqlServer.Management.Smo.Datatype]::NVarchar(25)
$tb.Columns.Add($colField3)
Now create the table by running the following code:
$tb.create ()

 

Here is the complete line-by-line code:

PS_SMOTable03.png
Again do not look out for a response but check for the table in Feb6 database you
created earlier.

PS_SMOTable04.png