Is it possible to view the partitioning that is set up inside a database ?

  • Hi

    First post, so be gentle ...

    One of my clients set up their database using an sql script which included their partitioning & Filegroup info ..
    I have just been notified that they have an error and asked for the sql that created the DB, unfortunately it has been deleted.
    Is there a way to extract the partitioning & Filegroup info from the database ?
    I can see (on my own example) that I can see the file group set up, but nothing about the partitioning.

    Any help or pointers appreciated.

    Steve

  • Steve

    You're probably going to have to reverse engineer it from the catalog views.  sys partitions is a good place to start.  You might even find someone who's done this before if you search for something like "script out partitions".

    John

  • Cheers John will take a look
    Will set up an example DB with a simple set up (partition & FG) and see if I can get the required info from that.

    Steve

  • To generate a script for the database, right-click on the database name in SSMS Object Explorer and select "Script database as...".
    To generate scripts for tables, including the partition schemes and partition functions, some scripting options must be set. In SSMS menu open the  Tools/Options  dialog and go to "SQL Server Object Explorer/Scripting". Under Table and view options, set "Script partition schemes to True. Under Object scripting options, set Generate script for dependent objects.
    Now table scripts generated from right clicking on tables will include the table partitioning information, the partition function and the partition scheme.
    HTH,
    Tim

  • will this help

    SELECT SCHEMA_NAME(so.schema_id) AS schema_name ,
       OBJECT_NAME(p.object_id) AS object_name ,
       p.partition_number ,
       p.data_compression_desc ,
       dbps.row_count ,
       dbps.reserved_page_count * 8 / 1024. AS reserved_mb ,
       si.index_id ,
       CASE WHEN si.index_id = 0 THEN '(heap!)'
          ELSE si.name
       END AS index_name ,
       si.is_unique ,
       si.data_space_id ,
       mappedto.name AS mapped_to_name ,
       mappedto.type_desc AS mapped_to_type_desc ,
       partitionds.name AS partition_filegroup ,
       pf.name AS pf_name ,
       pf.type_desc AS pf_type_desc ,
       pf.fanout AS pf_fanout ,
       pf.boundary_value_on_right ,
       ps.name AS partition_scheme_name ,
       rv.value AS range_value
    FROM  sys.partitions p
    JOIN  sys.objects so
       ON p.object_id = so.object_id
        AND so.is_ms_shipped = 0
    LEFT JOIN sys.dm_db_partition_stats AS dbps
       ON p.object_id = dbps.object_id
        AND p.partition_id = dbps.partition_id
    JOIN  sys.indexes si
       ON p.object_id = si.object_id
        AND p.index_id = si.index_id
    LEFT JOIN sys.data_spaces mappedto
       ON si.data_space_id = mappedto.data_space_id
    LEFT JOIN sys.destination_data_spaces dds
       ON si.data_space_id = dds.partition_scheme_id
        AND p.partition_number = dds.destination_id
    LEFT JOIN sys.data_spaces partitionds
       ON dds.data_space_id = partitionds.data_space_id
    LEFT JOIN sys.partition_schemes AS ps
       ON dds.partition_scheme_id = ps.data_space_id
    LEFT JOIN sys.partition_functions AS pf
       ON ps.function_id = pf.function_id
    LEFT JOIN sys.partition_range_values AS rv
       ON pf.function_id = rv.function_id
        AND dds.destination_id = CASE pf.boundary_value_on_right
                  WHEN 0 THEN rv.boundary_id
                  ELSE rv.boundary_id + 1 end

  • goher2000 - Thursday, November 30, 2017 10:46 AM

    will this help


    SELECT SCHEMA_NAME(so.schema_id) AS schema_name
       ,OBJECT_NAME(p.object_id) AS object_name
       ,p.partition_number
       ,p.data_compression_desc
       ,dbps.row_count
       ,dbps.reserved_page_count * 8 / 1024. AS reserved_mb
       ,si.index_id
       ,CASE
           WHEN si.index_id = 0
               THEN '(heap!)'
           ELSE si.name
           END AS index_name
       ,si.is_unique
       ,si.data_space_id
       ,mappedto.name AS mapped_to_name
       ,mappedto.type_desc AS mapped_to_type_desc
       ,partitionds.name AS partition_filegroup
       ,pf.name AS pf_name
       ,pf.type_desc AS pf_type_desc
       ,pf.fanout AS pf_fanout
       ,pf.boundary_value_on_right
       ,ps.name AS partition_scheme_name
       ,rv.value AS range_value
    FROM sys.partitions p
    INNER JOIN sys.objects so ON p.object_id = so.object_id
          AND so.is_ms_shipped = 0
    LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id
          AND p.partition_id = dbps.partition_id
    INNER JOIN sys.indexes si ON p.object_id = si.object_id
          AND p.index_id = si.index_id
    LEFT JOIN sys.data_spaces mappedto ON si.data_space_id = mappedto.data_space_id
    LEFT JOIN sys.destination_data_spaces dds ON si.data_space_id = dds.partition_scheme_id
          AND p.partition_number = dds.destination_id
    LEFT JOIN sys.data_spaces partitionds ON dds.data_space_id = partitionds.data_space_id
    LEFT JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
    LEFT JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
    LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id
          AND dds.destination_id = rv.boundary_id + CAST(pf.boundary_value_on_right AS int);

    Just gave it some formatting.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • thanks, it is readable now

  • Hi  thanks for all your comments, making some progress now

  • stevenmillward - Friday, December 15, 2017 10:37 AM

    Hi  thanks for all your comments, making some progress now

    What does that actually mean?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply