How can we find on which particular partition function basis the partition table is linked with from the sys tables?

Hello all,

I am having little problem with the finding the particular partition function of particular Tables related to the sys.partition tables.

Can anyone tell me that how can we find the particular partition function of particular table that linked with.

I am having problem with function_id and object_id of the sys.partitions tables related.

Is there any link between these to fields so that i can easily obtain the particular partition function of particular table that linked with.

Can any one help me out.

Thanks,

Anil Maharjan

Anil,

While I don't know the specific answer to your question, this Microsoft system table ( for 2008 ) map might help - http://www.microsoft.com/downloads/en/details.aspx?FamilyID=531c53e7-8a2a-4375-8f2f-5d799aa67b5c&displaylang=en

It shows the relationships of the various system views.

Hope it helps,

Keith

Hello all,

After a lot of research on this i finally got the solution using the script below hope that might help some one. :)

select distinct

  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

 --where p.object_id = object_id('TableName')

Thanks,

Anil Maharjan