How to query Partition Options?

• Tobias Kiemes

  SSCommitted

  Points: 1604

  More actions

  January 29, 2009 at 5:28 am

  #125994

  Hi,

  I try to generate DDL Statements for the tables in a database. For this i read all necessary information of the information_schema and sys-tables.

  But I got stuck with generating the partition clause:

  CREATE TABLE (

  ...

  ) ON [PS_Hash](partition_id)

  I can read the information, that the table is on the partition scheme "PS_Hash", but how do i find out the relavant column for the partition function "partition_id"?

  Here's my query:

  select tbl.name as tbl_name

  , tbl.lob_data_space_id

  , dstab.name dstab

  , idx.type_desc

  , idx.data_space_id

  , dsidx.name dsidx

  , CAST(CASE WHEN 'PS'=dsidx.type THEN 1 ELSE 0 END AS bit) AS [IsPartitioned]

  , ' ON [' + dsidx.name + '](how to go on?)' P_clause

  from sys.tables tbl

  left outer join sys.data_spaces dstab

  on tbl.lob_data_space_id = dstab.data_space_id

  INNER JOIN sys.indexes AS idx

  ON idx.object_id = tbl.object_id and idx.index_id < 2

  left outer join sys.data_spaces dsidx

  on idx.data_space_id = dsidx.data_space_id

  I already tried to profile the Statement-Generation of MS, but unfortunately it does not imply the storage..

  Tobias