分享一个SQLSERVER脚本（计算数据库中各个表的数据量和每行记录所占用空间） 很多时候我们都需要计算数据库中各个表的数据量和每行记录所占用空间 这里共享一个脚本 CREATE TABLE #tablespaceinfo ( nameinfo VARCHAR ( 500 ) , rowsinfo BIGINT , reserved

分享一个SQLSERVER脚本（计算数据库中各个表的数据量和每行记录所占用空间）

很多时候我们都需要计算数据库中各个表的数据量和每行记录所占用空间

这里共享一个脚本

<span>CREATE</span> <span>TABLE</span><span> #tablespaceinfo    (      nameinfo </span><span>VARCHAR</span>(<span>500</span><span>) ,      rowsinfo </span><span>BIGINT</span><span> ,      reserved </span><span>VARCHAR</span>(<span>20</span><span>) ,      datainfo </span><span>VARCHAR</span>(<span>20</span><span>) ,      index_size </span><span>VARCHAR</span>(<span>20</span><span>) ,      unused </span><span>VARCHAR</span>(<span>20</span><span>)    )   </span><span>DECLARE</span> <span>@tablename</span> <span>VARCHAR</span>(<span>255</span><span>);   </span><span>DECLARE</span> Info_cursor <span>CU<mark>来源gaodaimacom搞#^代%!码网</mark>RSOR</span><span>FOR</span>    <span>SELECT</span>  <span>'</span><span>[</span><span>'</span> <span>+</span> <span>[</span><span>name</span><span>]</span> <span>+</span> <span>'</span><span>]</span><span>'</span>    <span>FROM</span><span>    sys.tables    </span><span>WHERE</span>   type <span>=</span> <span>'</span><span>U</span><span>'</span><span>;   </span><span>OPEN</span><span> Info_cursor  </span><span>FETCH</span> <span>NEXT</span> <span>FROM</span> Info_cursor <span>INTO</span> <span>@tablename</span>   <span>WHILE</span> <span>@@FETCH_STATUS</span> <span>=</span> <span>0</span>    <span>BEGIN</span>         <span>INSERT</span>  <span>INTO</span><span> #tablespaceinfo                </span><span>EXEC</span> sp_spaceused <span>@tablename</span>          <span>FETCH</span> <span>NEXT</span> <span>FROM</span><span> Info_cursor      </span><span>INTO</span> <span>@tablename</span>      <span>END</span>  <span>CLOSE</span><span> Info_cursor  </span><span>DEALLOCATE</span><span> Info_cursor   </span><span>--</span><span>创建临时表</span><span>CREATE</span> <span>TABLE</span> <span>[</span><span>#tmptb</span><span>]</span><span>    (      TableName </span><span>VARCHAR</span>(<span>50</span><span>) ,      DataInfo </span><span>BIGINT</span><span> ,      RowsInfo </span><span>BIGINT</span><span> ,      Spaceperrow </span><span>AS</span> ( <span>CASE</span><span> RowsInfo                         </span><span>WHEN</span> <span>0</span> <span>THEN</span> <span>0</span>                         <span>ELSE</span> DataInfo <span>/</span><span> RowsInfo                       </span><span>END</span><span> ) PERSISTED    )</span><span>--</span><span>插入数据到临时表</span><span>INSERT</span>  <span>INTO</span> <span>[</span><span>#tmptb</span><span>]</span><span>        ( </span><span>[</span><span>TableName</span><span>]</span><span> ,          </span><span>[</span><span>DataInfo</span><span>]</span><span> ,          </span><span>[</span><span>RowsInfo</span><span>]</span><span>        )        </span><span>SELECT</span>  <span>[</span><span>nameinfo</span><span>]</span><span> ,                </span><span>CAST</span>(<span>REPLACE</span>(<span>[</span><span>datainfo</span><span>]</span>, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>BIGINT</span>) <span>AS</span> <span>'</span><span>datainfo</span><span>'</span><span> ,                </span><span>[</span><span>rowsinfo</span><span>]</span>        <span>FROM</span><span>    #tablespaceinfo        </span><span>ORDER</span> <span>BY</span> <span>CAST</span>(<span>REPLACE</span>(reserved, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>INT</span>) <span>DESC</span>  <span>--</span><span>汇总记录</span><span>SELECT</span>  <span>[</span><span>tbspinfo</span><span>]</span>.<span>*</span><span> ,        </span><span>[</span><span>tmptb</span><span>]</span>.<span>[</span><span>Spaceperrow</span><span>]</span> <span>AS</span> <span>'</span><span>每行记录大概占用空间（KB）</span><span>'</span><span>FROM</span>    <span>[</span><span>#tablespaceinfo</span><span>]</span> <span>AS</span><span> tbspinfo ,        </span><span>[</span><span>#tmptb</span><span>]</span> <span>AS</span><span> tmptb</span><span>WHERE</span>   <span>[</span><span>tbspinfo</span><span>]</span>.<span>[</span><span>nameinfo</span><span>]</span> <span>=</span> <span>[</span><span>tmptb</span><span>]</span>.<span>[</span><span>TableName</span><span>]</span><span>ORDER</span> <span>BY</span> <span>CAST</span>(<span>REPLACE</span>(<span>[</span><span>tbspinfo</span><span>]</span>.<span>[</span><span>reserved</span><span>]</span>, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>INT</span>) <span>DESC</span>  <span>DROP</span> <span>TABLE</span> <span>[</span><span>#tablespaceinfo</span><span>]</span><span>DROP</span> <span>TABLE</span> <span>[</span><span>#tmptb</span><span>]</span>

喜欢 (0)赏

[搞代码]

分享 (0)