This recipe shows how to create a table using PowerShell and SMO.
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