{"id":89,"date":"2022-11-16T14:21:35","date_gmt":"2022-11-16T17:21:35","guid":{"rendered":"https:\/\/kappasoft.com.br\/wiki\/?p=89"},"modified":"2022-11-16T14:21:36","modified_gmt":"2022-11-16T17:21:36","slug":"saber-o-tamanho-das-tabelas-no-sql-server","status":"publish","type":"post","link":"https:\/\/kappasoft.com.br\/wiki\/2022\/11\/16\/saber-o-tamanho-das-tabelas-no-sql-server\/","title":{"rendered":"Saber o tamanho das tabelas no SQL Server"},"content":{"rendered":"\n<div class=\"wp-block-codemirror-blocks-code-block code-block\"><pre class=\"CodeMirror\" data-setting=\"{&quot;showPanel&quot;:true,&quot;languageLabel&quot;:&quot;language&quot;,&quot;fullScreenButton&quot;:true,&quot;copyButton&quot;:true,&quot;mode&quot;:&quot;sql&quot;,&quot;mime&quot;:&quot;text\/x-sql&quot;,&quot;theme&quot;:&quot;material&quot;,&quot;lineNumbers&quot;:false,&quot;styleActiveLine&quot;:false,&quot;lineWrapping&quot;:false,&quot;readOnly&quot;:true,&quot;fileName&quot;:&quot;&quot;,&quot;language&quot;:&quot;SQL&quot;,&quot;maxHeight&quot;:&quot;400px&quot;,&quot;modeName&quot;:&quot;sql&quot;}\">select\n    a3.name as proprietario,\n    a2.name as tabela,\n    a1.rows as registros,\n    ((a1.reserved + isnull(a4.reserved,0)) * 8)\/1024\/1024 as 'Espa\u00e7o Total (GB)', \n    a1.data * 8 as 'Dados (kb)',\n    (case when (a1.used + isnull(a4.used,0)) &gt; a1.data then (a1.used + isnull(a4.used,0)) - a1.data else 0 end) * 8 as '\u00cdndices (kb)',\n    (case when (a1.reserved + isnull(a4.reserved,0)) &gt; a1.used then (a1.reserved + isnull(a4.reserved,0)) - a1.used else 0 end) * 8 as 'N\u00e3o Usado (kb)'\nfrom (\n    select\n        ps.object_id,\n            sum (\n                case\n                    when (ps.index_id &lt; 2) then row_count\n                    else 0\n                end\n                ) as [rows],\n            sum (ps.reserved_page_count) as reserved,\n            sum (\n                case\n                    when (ps.index_id &lt; 2) then (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)\n                    else (ps.lob_used_page_count + ps.row_overflow_used_page_count)\n                end\n                ) as data,\n            sum (ps.used_page_count) as used\n    from sys.dm_db_partition_stats ps\n    where ps.object_id not in (select object_id from sys.tables where is_memory_optimized = 1)\n    group by ps.object_id) as a1\nleft outer join (\n    select\n        it.parent_id,\n        sum(ps.reserved_page_count) as reserved,\n        sum(ps.used_page_count) as used\n     from sys.dm_db_partition_stats ps\n     inner join sys.internal_tables it on (it.object_id = ps.object_id)\n     where it.internal_type in (202, 204)\n     group by it.parent_id) as a4 on (a4.parent_id = a1.object_id)\ninner join sys.all_objects a2  on ( a1.object_id = a2.object_id)\ninner join sys.schemas a3 on (a2.schema_id = a3.schema_id)\nwhere a2.type &lt;&gt; 's' and a2.type &lt;&gt; 'it'\nand a3.name like '%%' --esquema\nand a2.name like '%%' --tabela\norder by a3.name, a2.name<\/pre><\/div>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,3],"tags":[],"class_list":["post-89","post","type-post","status-publish","format-standard","hentry","category-banco-de-dados","category-sql-server"],"_links":{"self":[{"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/posts\/89","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/comments?post=89"}],"version-history":[{"count":1,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/posts\/89\/revisions"}],"predecessor-version":[{"id":90,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/posts\/89\/revisions\/90"}],"wp:attachment":[{"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/media?parent=89"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/categories?post=89"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/tags?post=89"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}