Find Partition Information in a SQL Server 2005/2008 Table using T-SQL

In my previous article http://www.sqlservercurry.com/2009/08/how-to-partition-tables-of.html , I demonstrated how to use a ready made script provided by Microsoft to create partitions on the AdventureWorks database. A user left a comment - “How can I programmatically determine the tables that have partitions and also determine information like PartitionScheme.

Here’s how -

SELECT DISTINCT part.object_id as ObjectID,
OBJECT_NAME(part.object_id) as TableNm,
idx.name as IndexNm,
idx.type_desc as IndexType,
psch.name as PartitionScheme,
pfun.name as FunctionName
FROM sys.partitions part
INNER JOIN sys.indexes idx
ON part.[object_id] = idx.[object_id] and part.index_id = idx.index_id
INNER JOIN sys.data_spaces dsp
ON idx.data_space_id = dsp.data_space_id
INNER JOIN sys.partition_schemes psch
ON dsp.data_space_id = psch.data_space_id
INNER JOIN sys.partition_functions pfun
ON psch.function_id = pfun.function_id

OUTPUT

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: