{"id":66,"date":"2013-03-15T15:55:00","date_gmt":"2013-03-15T20:55:00","guid":{"rendered":"https:\/\/www.chrystalsander.com\/blog\/?p=66"},"modified":"2023-07-26T12:34:20","modified_gmt":"2023-07-26T17:34:20","slug":"sql-server-query-for-a-quick-overview-of-your-partitions","status":"publish","type":"post","link":"https:\/\/www.chrystalsander.com\/blog\/?p=66","title":{"rendered":"SQL Server : Query For A Quick Overview Of Your Partitions"},"content":{"rendered":"
\n
\n

— =============================================<\/o:p><\/span><\/p>\n

— This is a basic query intended to provide<\/o:p><\/span><\/p>\n

— a quick look at partition info.   <\/o:p><\/span><\/p>\n

— =============================================<\/o:p><\/span><\/p>\n

 <\/o:p><\/span><\/p>\n

SELECT<\/span> b.<\/span>name as<\/span> ‘table_name’<\/span>,<\/span> a.<\/span>partition_number,<\/o:p><\/span><\/span><\/p>\n

a.<\/span>[rows] as<\/span> ‘row_count’<\/span>,<\/span> a.<\/span>partition_id,<\/span> <\/o:p><\/span><\/p>\n

a.<\/span>object_id<\/span>,<\/span> a.<\/span>hobt_id,<\/span> <\/o:p><\/span><\/p>\n

b.<\/span>schema_id<\/span>,<\/span> c.<\/span>name as<\/span> ‘schema_name’<\/span>,<\/span>  <\/o:p><\/span><\/p>\n

a.<\/span>index_id,<\/span> b.<\/span>create_date,<\/span> b.<\/span>modify_date<\/o:p><\/span><\/p>\n

FROM<\/span>  sys<\/span>.<\/span>partitions<\/span> AS<\/span> a INNER<\/span> JOIN<\/o:p><\/span><\/span><\/p>\n

sys<\/span>.<\/span>objects<\/span> AS<\/span> b ON<\/span> a.<\/span>object_id<\/span> =<\/span> b.<\/span>object_id<\/o:p><\/span><\/span><\/p>\n

INNER<\/span> JOIN<\/span> sys<\/span>.<\/span>schemas<\/span> AS<\/span> c On<\/span> c.<\/span>schema_id<\/span> =<\/span> b.<\/span>schema_id<\/span>               <\/o:p><\/span><\/p>\n

WHERE<\/span> LOWER<\/span>(<\/span>b.<\/span>name)<\/span> =<\/span> ‘yourPartitionedTableName’<\/o:p><\/span><\/span><\/p>\n

–pull only first index for a simpler view<\/o:p><\/span><\/p>\n

and<\/span> a.<\/span>index_id <<\/span> 2 <\/o:p><\/span><\/p>\n

ORDER<\/span> BY<\/span> b.<\/span>name,<\/span> partition_number;<\/o:p><\/span><\/span><\/p>\n

 <\/o:p><\/span><\/p>\n

— =============================================<\/o:p><\/span><\/p>\n

–Helpful info for a deeper dive<\/o:p><\/span><\/p>\n

— =============================================<\/o:p><\/span><\/p>\n

 <\/o:p><\/span><\/p>\n

SELECT<\/span> *<\/span> FROM<\/span> SYS<\/span>.<\/span>partition_range_values<\/span> ;<\/o:p><\/span><\/span><\/p>\n

SELECT<\/span> *<\/span> from<\/span> sys<\/span>.<\/span>partitions<\/span>;<\/o:p><\/span><\/span><\/p>\n

SELECT<\/span> *<\/span> FROM<\/span> SYS<\/span>.<\/span>partition_functions<\/span>;<\/o:p><\/span><\/span><\/p>\n

SELECT<\/span> *<\/span> FROM<\/span> sys<\/span>.<\/span>partition_schemes<\/span>;<\/o:p><\/span><\/span><\/p>\n

SELECT<\/span> *<\/span> from<\/span> sys<\/span>.<\/span>schemas<\/span>;<\/o:p><\/span><\/span><\/p>\n

SELECT<\/span> *<\/span> FROM<\/span> sys<\/span>.<\/span>filegroups<\/span>;<\/o:p><\/span><\/span><\/p>\n

 <\/o:p><\/span><\/p>\n

–query sys.partitions to view and verify that the table contains partitions as expected<\/o:p><\/span><\/p>\n

SELECT<\/span> *<\/span> FROM<\/span> sys<\/span>.<\/span>partitions<\/span> where<\/span> OBJECT_NAME<\/span>(<\/span>Object_Id<\/span>)=<\/span>‘yourPartitionedTableName’<\/span>;<\/o:p><\/span><\/span><\/p>\n

SELECT<\/span> *<\/span> FROM<\/span> sys<\/span>.<\/span>objects<\/span> where<\/span> OBJECT_NAME<\/span>(<\/span>Object_Id<\/span>)=<\/span>‘yourPartitionedTableName’<\/span>;<\/span><\/span><\/o:p><\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

— ============================================= — This is a basic query intended to provide — a quick look at partition info.   — =============================================   SELECT b.name as ‘table_name’, a.partition_number, a.[rows] as ‘row_count’, a.partition_id, a.object_id, a.hobt_id, b.schema_id, c.name as ‘schema_name’,  a.index_id, b.create_date, b.modify_date FROM  sys.partitions AS a INNER JOIN sys.objects AS b ON a.object_id = b.object_id INNER JOIN sys.schemas … Continue reading “SQL Server : Query For A Quick Overview Of Your Partitions”<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[52,50],"_links":{"self":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/66"}],"collection":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=66"}],"version-history":[{"count":1,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/66\/revisions"}],"predecessor-version":[{"id":178,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/66\/revisions\/178"}],"wp:attachment":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=66"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=66"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=66"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}