How to determine a table is partitioned or not and the partition column

Table partition is a new feature in SQL Server 2005. To partition a table, we need to define partition function, partition
scheme and use the scheme in table definition. However, how to check a table is partitioned or not? what columns the table is
partitioned?
The following SQL Server catalog views about partitions do not give a clue:
sys.partition_functions
sys.partition_parameters
sys.partition_range_values
sys.partition_schemes
sys.partitions
Actually, the information is in the sys.data_spaces and sys.index_columns catalog view. If a table does not have cluster idnex (Heap), the index id will be 0. A data space is a filegroup or partition scheme.
 
To check a table is partitioned or not and its partition column:
select c.name AS PartitionColumn,ic.partition_ordinal AS PartitionColOrder,dsidx.[name] AS PartitionScheme
from sys.indexes AS idx, sys.columns c,
 sys.index_columns ic, sys.data_spaces AS dsidx
where idx.[object_id]=object_id(‘dbo.YouTable’)
 and idx.index_id<2
 and idx.[object_id]=ic.[object_id]
 and idx.index_id=ic.index_id
 and dsidx.data_space_id = idx.data_space_id
 and dsidx.type=N’PS’
 and c.object_id=ic.[object_id]
 and c.column_id=ic.column_id
 and ic.partition_ordinal>0
You can test on the following tables:
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
ALL TO ([PRIMARY]) ;
GO
CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO
CREATE TABLE PartitionTable1 (col1 int, col2 char(10),col3 int not null primary key clustered (col3))
ON myRangePS1 (col3)
go
CREATE TABLE PartitionTable2 (col1 int not null, col2 char(10),col3 int primary key clustered (col3,col1))
ON myRangePS1 (col3)
CREATE TABLE PartitionTable3 (col1 int not null,col4 int not null, col2 char(10),col3 int primary key clustered
(col1,col3,col4))
ON myRangePS1 (col3)
 
Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s