{"id":592,"date":"2016-10-04T04:12:56","date_gmt":"2016-10-04T04:12:56","guid":{"rendered":"http:\/\/oranotes.tuktaroff.com\/?page_id=592"},"modified":"2016-10-04T04:12:56","modified_gmt":"2016-10-04T04:12:56","slug":"indextable-size","status":"publish","type":"page","link":"http:\/\/oranotes.tuktaroff.com\/?page_id=592","title":{"rendered":"Index\/Table size"},"content":{"rendered":"<p><code><br \/>\n<script type=\"text\/javascript\"> \nfunction copy(what) {\n    window.clipboardData.setData('Text',what.value);\n}\nfunction paste(what) {\n    what.value = window.clipboardData.getData('Text');\n}\n<\/script><\/p>\n<h2>\nSize of tables and indexes<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nSELECT object_name(i.object_id) as objectName,\r\ni.[name] as indexName,\r\nsum(a.total_pages) as totalPages,\r\nsum(a.used_pages) as usedPages,\r\nsum(a.data_pages) as dataPages,\r\n(sum(a.total_pages) * 8) \/ 1024 as totalSpaceMB,\r\n(sum(a.used_pages) * 8) \/ 1024 as usedSpaceMB, \r\n(sum(a.data_pages) * 8) \/ 1024 as dataSpaceMB\r\nFROM sys.indexes i\r\nINNER JOIN sys.partitions p\r\nON i.object_id = p.object_id\r\nAND i.index_id = p.index_id\r\nINNER JOIN sys.allocation_units a\r\nON p.partition_id = a.container_id\r\nGROUP BY i.object_id, i.index_id, i.[name]\r\nORDER BY sum(a.total_pages) DESC, object_name(i.object_id)\r\nGO\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nSELECT object_name(i.object_id) as objectName,<br \/>\ni.[name] as indexName,<br \/>\nsum(a.total_pages) as totalPages,<br \/>\nsum(a.used_pages) as usedPages,<br \/>\nsum(a.data_pages) as dataPages,<br \/>\n(sum(a.total_pages) * 8) \/ 1024 as totalSpaceMB,<br \/>\n(sum(a.used_pages) * 8) \/ 1024 as usedSpaceMB,<br \/>\n(sum(a.data_pages) * 8) \/ 1024 as dataSpaceMB<br \/>\nFROM sys.indexes i<br \/>\nINNER JOIN sys.partitions p<br \/>\nON i.object_id = p.object_id<br \/>\nAND i.index_id = p.index_id<br \/>\nINNER JOIN sys.allocation_units a<br \/>\nON p.partition_id = a.container_id<br \/>\nGROUP BY i.object_id, i.index_id, i.[name]<br \/>\nORDER BY sum(a.total_pages) DESC, object_name(i.object_id)<br \/>\nGO<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<p><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Size of tables and indexes SELECT object_name(i.object_id) as objectName, i.[name] as indexName, sum(a.total_pages) as totalPages, sum(a.used_pages) as usedPages, sum(a.data_pages) as dataPages, (sum(a.total_pages) * 8) \/ 1024 as totalSpaceMB, (sum(a.used_pages) * 8) \/ 1024 as usedSpaceMB, (sum(a.data_pages) * 8) \/ 1024 as dataSpaceMB FROM sys.indexes i INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":576,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-592","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/P2NVny-9y","_links":{"self":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/592","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=592"}],"version-history":[{"count":1,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/592\/revisions"}],"predecessor-version":[{"id":593,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/592\/revisions\/593"}],"up":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/576"}],"wp:attachment":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=592"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}