Wednesday, February 2, 2011

Scripting Partitioned Tables From object Explorer;

One of our developer mentioned while scripting INDIVIDUAL partitioned tables from the database the Partition Scheme wasn't included in his script. For example, I created a database and a table on partiton scheme named TestPS. The Partiton Function is TestPF. But When I script that table from object explorer it doesn't include the partition scheme unless we change the scripting option.

But Once I change that option, under Tools-Options->SQL Server ObjectExplorer->Scripting . On the Right under Table and View options-> We need to set Partition Schemes to True So the script includes the partiton Scheme the table was created on.


 CREATE TABLE [dbo].[TestPart]([CurrentTime] [datetime] NOT NULL,[Id] [int] NULL,PRIMARY KEY CLUSTERED ([CurrentTime] ASC)
)
ON [TestPS]([CurrentTime]) ON [TestPS]([CurrentTime])

GO


This is the Partition Function and Partition Scheme I used to create the table.
Thanks

USE [TEST]
GO/
****** Object: PartitionFunction [TestPF] Script Date: 02/02/2011 09:17:08 ******/
CREATE PARTITION FUNCTION [TestPF](datetime) AS RANGE RIGHT FOR VALUES (N'2011-01-31T23:59:59.997', N'2011-02-28T23:59:59.997')
GO
USE [TEST]
GO
/****** Object: PartitionScheme [TestPS] Script Date: 02/02/2011 09:16:59 ******/
CREATE PARTITION SCHEME [TestPS] AS PARTITION [TestPF] TO ([PRIMARY], [PRIMARY], [PRIMARY])
GO



/****** Object: Table [dbo].[TestPart] Script Date: 02/02/2011 09:28:26 ******/
CREATE TABLE [dbo].[TestPart]([CurrentTime] [datetime] NOT NULL,[Id] [int] NULL,PRIMARY KEY CLUSTERED ([CurrentTime] ASC))


/****** Object: Table [dbo].[TestPart] Script Date: 02/02/2011 09:16:08 ******/
 

CREATE TABLE [dbo].[TestPart]([CurrentTime] [datetime] NOT NULL,[Id] [int] NULL,PRIMARY KEY CLUSTERED ([CurrentTime] ASC))