Creating a table

This recipe shows how to create a table using PowerShell and SMO.

Getting ready

We will use the AdventureWorks2014 database to create a table named Student, which has five columns and two default constraints. To give you a better idea of what we are trying to achieve, the equivalent T-SQL script needed to create this table is as follows:

USE AdventureWorks2014
GO
CREATE TABLE [dbo].[Student](
  [StudentID] [INT] IDENTITY(1,1) NOT NULL,
  [FName] [VARCHAR](50) NULL,
  [LName] [VARCHAR](50) NOT NULL,
  [DateOfBirth] [DATETIME] NULL,
  [Age]  AS (DATEPART(YEAR,GETDATE())-DATEPART(YEAR,[DateOfBirth])),
    CONSTRAINT [PK_Student_StudentID] PRIMARY KEY CLUSTERED
   (
     [StudentID] ASC
   )
)

GO
ALTER TABLE [dbo].[Student] ADD  CONSTRAINT [DF_Student_LName] DEFAULT ('Doe') FOR [LName]
GO

ALTER TABLE [dbo].[Student] ADD  CONSTRAINT [DF_Student_DateOfBirth]  DEFAULT ('1800-00-00') FOR [DateOfBirth]
GO

How to do it...

Let's create the Student table using PowerShell:

1. Open PowerShell...