This recipe shows how to create a table using PowerShell and SMO.
We will use the AdventureWorks2008R2 database to create a table named Student, which has five columns. 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 AdventureWorks2008R2 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
Let's create the Student
table using PowerShell:
Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.
Import the
SQLPSmodule, and create a new SMO Server object:#import SQL Server module Import-Module SQLPS -DisableNameChecking #replace this with your instance name...