July 9, 2008 at 10:55 am
Hello,
Given a table name, I would like to know via query :
a) whether the table is partitioned or not
b) If partitioned, what is the name of the partition function, name of the partition scheme
Is there a way by querying the catalog tables, I would get this info?.
Thanks for any help!
Ganesh
July 9, 2008 at 11:03 am
Hi,
To your first question use the below query
SELECT * From sys.partitions
WHERE OBJECT_NAME(object_id) = 'Employee'
I am not sure about your second query, Even i am also lookin for that.
Thanks -- Vj
July 9, 2008 at 11:46 am
Found it, here it is:
select distinct
p.[object_id],
TbName = OBJECT_NAME(p.[object_id]),
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
-Ganesh
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply