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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook