CODEWITHSUNDEEP
sql-serversql-server-2008-r2partitiondatabase-partitioning
srh
srh

Reputation: 1661

How to visually see partition schemes in SQL Server Management Studio?

How can I visually see partition schemes in SQL Server Management Studio? I don't see them anywhere in the database.

Upvotes: 1

Views: 4847

Answers (1)

I A Khan
I A Khan

Reputation: 8859

To See Individual table partition schemes use this 

SELECT DISTINCT P_Sch.NAME  AS PartitionScheme, 
                P_Fun.NAME  AS PartitionFunction, 
                F_Grp.NAME  AS FileGroupName, 
                P_Range.VALUE AS PartitionFunctionValue 
FROM   sys.INDEXES Indx 
       JOIN sys.PARTITIONS Parti 
         ON Indx.OBJECT_ID = Parti.OBJECT_ID 
            AND Indx.INDEX_ID = Parti.INDEX_ID 
       JOIN sys.PARTITION_SCHEMES P_Sch 
         ON P_Sch.DATA_SPACE_ID = Indx.DATA_SPACE_ID 
       JOIN sys.PARTITION_FUNCTIONS P_Fun 
         ON P_Fun.FUNCTION_ID = P_Sch.FUNCTION_ID 
       LEFT JOIN sys.PARTITION_RANGE_VALUES P_Range 
              ON P_Range.FUNCTION_ID = P_Fun.FUNCTION_ID 
                 AND P_Range.BOUNDARY_ID = Parti.PARTITION_NUMBER 
       JOIN sys.ALLOCATION_UNITS A_Unit 
         ON A_Unit.CONTAINER_ID = Parti.HOBT_ID 
       JOIN sys.FILEGROUPS F_Grp 
         ON F_Grp.DATA_SPACE_ID = A_Unit.DATA_SPACE_ID 
WHERE  Indx.OBJECT_ID = Object_id('YourTableName')

For more information visit

http://www.itprotoday.com/software-development/find-out-whats-going-inside-your-partitions

Upvotes: 3

Related Questions