How to identify partitioned tables within a database

List of partitioned tables, indexes in database

select distinct
p.[object_id],
OBJECT_NAME(p.[object_id]) as table_name,
index_name = i.[name],
index_type_desc = i.type_desc,
partition_scheme = ps.[name],
data_space_id = ps.data_space_id,
function_name = pf.[name],
function_id = ps.function_id
from sys.partitions p
inner join sys.indexes i
on p.[object_id] = i.[object_id]
and p.index_id = i.index_id
inner join sys.data_spaces ds
on i.data_space_id = ds.data_space_id
inner join sys.partition_schemes ps
on ds.data_space_id = ps.data_space_id
inner JOIN sys.partition_functions pf
on ps.function_id = pf.function_id
order by table_name, index_name ;

List of partitioned tables along with partition column

SELECT distinct object_name (c.object_id) AS table_name,
c.[name] AS partitioned_column
FROM sys.index_columns ic
JOIN sys.columns c
ON ic.object_ID=c.object_ID
AND ic.Column_ID=c.Column_ID
WHERE ic.Partition_ordinal=1

Advertisements

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