搜索
首页数据库mysql教程[MSSQL]分享一个SQLSERVER脚本（计算数据库中各个表的数据量和每

[MSSQL]分享一个SQLSERVER脚本（计算数据库中各个表的数据量和每

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Jun 07, 2016 pm 03:17 PM
mssqlsqlserver分享数据脚本计算

分享一个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><strong>500</strong></span><span>) ,
      rowsinfo </span><span>BIGINT</span><span> ,
      reserved </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) ,
      datainfo </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) ,
      index_size </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) ,
      unused </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>)
    )  
 
</span><span>DECLARE</span> <span>@tablename</span> <span>VARCHAR</span>(<span><strong>255</strong></span><span>);  
 
</span><span>DECLARE</span> Info_cursor <span>CURSOR</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><strong>@@FETCH_STATUS</strong></span> <span>=</span> <span><strong>0</strong></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><strong>50</strong></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><strong>0</strong></span> <span>THEN</span> <span><strong>0</strong></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>

注意：使用之前要计算哪个数据库的记录，请先USE一下要统计表记录数的那个数据库！！

 

工作中遇到的问题

可以说我在实际的工作中 ，在100个问题中有90个都会先用到这个脚本

这里举一个我本人工作中遇到的一些问题

 

问题一：

程序员反映数据库查询慢，5分钟还没有出结果

我先用这个脚本看一下这个表有多少记录，大概有1000w 条数据

然后在本地的SSMS里查询，确实也是大概4分钟的样子才出来数据，看一下执行计划，发现查询能使用到索引

看一下数据库的压力，并不是很大，我跟会不会跟数据量有关系呢？

程序员要查询的结果条数是500条数据，业务表是做了分区的，按道理应该不会慢成这样。。。

后来我再看一下共享出来的那个脚本的结果，发现查询的结果大小=每行记录的大小*记录数

要查询大概500MB的数据，再传到客户端，不慢才怪

 

为什麽查询出的结果这么大？

主要是有几个大字段：例如：二进制字段和NVARCHAR(MAX)

并且时间范围跨度比较大

 

马上叫程序员改一下查询的语句，由于是entity framework程序，怎麽改我就不太清楚了，主要是不必要的字段就不查询处理并且缩小时间范围

 

问题二：

还有一些问题也需要知道每行记录的大小，例如删除表的历史数据，QA说要保留2013年之前的数据，你需要查出保留的数据或者2013年之前的数据占用多少G空间

再结合当前服务器的磁盘可用空间，来评估删除的数据是否太多或者太少

那么流程是：先查出2013年之前的记录数有多少-》计算表的总记录数-》计算表的大小-》手工计算每行记录的大小-》乘以2013年之前的记录数

如果没有每行记录数这个字段，那么你手工计算，是不是效率就变慢了？？？

 

问题三：

导数据的时候，你想知道当前已经导了多少数据了，那么执行一下这个脚本就可以了，这个脚本基本不会被阻塞

很快就能查出结果

脚本的计算方法

方法一

实际上利用的就是数据行大小的信息除以记录数

<span>CASE</span><span> RowsInfo
</span><span>WHEN</span> <span><strong>0</strong></span> <span>THEN</span> <span><strong>0</strong></span>
<span>ELSE</span> DataInfo <span>/</span> RowsInfo

 

 

 方法二

<span>SELECT</span> <span>AVG</span>(<span>DATALENGTH</span>(C0))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C1))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C2))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C3)) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>TB106</span><span>]</span>

 

说一下两种方法的区别

第一种方法是效率高，当表有上亿条记录的时候，如果你使用第二种方法执行AVG(DATALENGTH(C0))是很慢的，因为SQLSERVER要统计字段大小信息

可能十几分钟都出不来结果

 

当然，第一种方法也有一些缺陷，就是当表的记录数少的时候，统计出来的每行记录占用空间是不准确的

因为datainfo这个值是以数据页大小为单位的，因为就算表只有一条记录，那么也会占用一个数据页（8KB）

那么当8KB/1 =8KB，一条记录肯定不会是8KB大小的，所以记录少的时候会不准确

但是当记录数很多的时候，就准确了

[MSSQL]分享一个SQLSERVER脚本（计算数据库中各个表的数据量和每

 

看一下TB106这个表统计出来的结果值

[MSSQL]分享一个SQLSERVER脚本（计算数据库中各个表的数据量和每

<span>SELECT</span> <span>AVG</span>(<span>DATALENGTH</span>(C0))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C1))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C2))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C3)) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>TB106</span><span>]</span>

[MSSQL]分享一个SQLSERVER脚本（计算数据库中各个表的数据量和每

[MSSQL]分享一个SQLSERVER脚本（计算数据库中各个表的数据量和每

可以看到是比较准确的

 

注意：

无论方法一还是方法二都不包括索引所占用的空间 ！！

[MSSQL]分享一个SQLSERVER脚本（计算数据库中各个表的数据量和每

总结

大家平时一定会想：究竟DBA有什么作用？

在这里就给大家一个例子了，在工作中，程序员是不会关心他要查询的数据的大小的，他不管三七二十一只要把数据select出来就行了，然后收工

DBA这里就要解决数据查询不出来的问题，一般的程序员觉得查询500条数据是很少的，根本不会关心表设计，表的字段的数据类型

当工作越来越多，开发任务越来越重的时候更是这样



 

所以本人觉得DBA这个角色还是比较重要的o(∩_∩)o 

 

如有不对的地方，欢迎大家拍砖o(∩_∩)o 

 

 

2014-7-7 脚本bug修复

由于算出来每行记录的精度有问题，我又对脚本的精度进行了改进

<span>CREATE</span> <span>TABLE</span><span> #tablespaceinfo
    (
      nameinfo </span><span>VARCHAR</span>(<span><strong>500</strong></span><span>) ,
      rowsinfo </span><span>BIGINT</span><span> ,
      reserved </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) ,
      datainfo </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) ,
      index_size </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) ,
      unused </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>)
    )  
 
</span><span>DECLARE</span> <span>@tablename</span> <span>VARCHAR</span>(<span><strong>255</strong></span><span>);  
 
</span><span>DECLARE</span> Info_cursor <span>CURSOR</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><strong>@@FETCH_STATUS</strong></span> <span>=</span> <span><strong>0</strong></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><strong>50</strong></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><strong>0</strong></span> <span>THEN</span> <span><strong>0</strong></span>
                         <span>ELSE</span> <span>CAST</span>(DataInfo <span>AS</span> <span>decimal</span>(<span><strong>18</strong></span>,<span><strong>2</strong></span>))<span>/</span><span>CAST</span>(RowsInfo <span>AS</span> <span>decimal</span>(<span><strong>18</strong></span>,<span><strong>2</strong></span><span>))
                       </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>

[MSSQL]分享一个SQLSERVER脚本（计算数据库中各个表的数据量和每

 

声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
相关文章
解释InnoDB缓冲池及其对性能的重要性。解释InnoDB缓冲池及其对性能的重要性。Apr 19, 2025 am 12:24 AM

InnoDBBufferPool通过缓存数据和索引页来减少磁盘I/O，提升数据库性能。其工作原理包括：1.数据读取：从BufferPool中读取数据；2.数据写入：修改数据后写入BufferPool并定期刷新到磁盘；3.缓存管理：使用LRU算法管理缓存页；4.预读机制：提前加载相邻数据页。通过调整BufferPool大小和使用多个实例，可以优化数据库性能。

MySQL与其他编程语言：一种比较MySQL与其他编程语言：一种比较Apr 19, 2025 am 12:22 AM

MySQL与其他编程语言相比，主要用于存储和管理数据，而其他语言如Python、Java、C 则用于逻辑处理和应用开发。 MySQL以其高性能、可扩展性和跨平台支持着称，适合数据管理需求，而其他语言在各自领域如数据分析、企业应用和系统编程中各有优势。

学习MySQL：新用户的分步指南学习MySQL：新用户的分步指南Apr 19, 2025 am 12:19 AM

MySQL值得学习，因为它是强大的开源数据库管理系统，适用于数据存储、管理和分析。1）MySQL是关系型数据库，使用SQL操作数据，适合结构化数据管理。2）SQL语言是与MySQL交互的关键，支持CRUD操作。3）MySQL的工作原理包括客户端/服务器架构、存储引擎和查询优化器。4）基本用法包括创建数据库和表，高级用法涉及使用JOIN连接表。5）常见错误包括语法错误和权限问题，调试技巧包括检查语法和使用EXPLAIN命令。6）性能优化涉及使用索引、优化SQL语句和定期维护数据库。

MySQL：初学者的基本技能MySQL：初学者的基本技能Apr 18, 2025 am 12:24 AM

MySQL适合初学者学习数据库技能。1.安装MySQL服务器和客户端工具。2.理解基本SQL查询，如SELECT。3.掌握数据操作：创建表、插入、更新、删除数据。4.学习高级技巧：子查询和窗口函数。5.调试和优化：检查语法、使用索引、避免SELECT*，并使用LIMIT。

MySQL：结构化数据和关系数据库MySQL：结构化数据和关系数据库Apr 18, 2025 am 12:22 AM

MySQL通过表结构和SQL查询高效管理结构化数据，并通过外键实现表间关系。1.创建表时定义数据格式和类型。2.使用外键建立表间关系。3.通过索引和查询优化提高性能。4.定期备份和监控数据库确保数据安全和性能优化。

MySQL：解释的关键功能和功能MySQL：解释的关键功能和功能Apr 18, 2025 am 12:17 AM

MySQL是一个开源的关系型数据库管理系统，广泛应用于Web开发。它的关键特性包括：1.支持多种存储引擎，如InnoDB和MyISAM，适用于不同场景；2.提供主从复制功能，利于负载均衡和数据备份；3.通过查询优化和索引使用提高查询效率。

SQL的目的：与MySQL数据库进行交互SQL的目的：与MySQL数据库进行交互Apr 18, 2025 am 12:12 AM

SQL用于与MySQL数据库交互，实现数据的增、删、改、查及数据库设计。1）SQL通过SELECT、INSERT、UPDATE、DELETE语句进行数据操作；2）使用CREATE、ALTER、DROP语句进行数据库设计和管理；3）复杂查询和数据分析通过SQL实现，提升业务决策效率。

初学者的MySQL：开始数据库管理初学者的MySQL：开始数据库管理Apr 18, 2025 am 12:10 AM

MySQL的基本操作包括创建数据库、表格，及使用SQL进行数据的CRUD操作。1.创建数据库：CREATEDATABASEmy_first_db;2.创建表格：CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(100)NOTNULL,published_yearINT);3.插入数据：INSERTINTObooks(title,author,published_year)VA

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序，用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

显示更多

热门文章

刺客信条阴影：贝壳谜语解决方案
3 周前ByDDD
Windows 11 KB5054979中的新功能以及如何解决更新问题
2 周前ByDDD
在哪里可以找到原子中的起重机控制钥匙卡
3 周前ByDDD
节省R.E.P.O.解释（并保存文件）
1 个月前By尊渡假赌尊渡假赌尊渡假赌
刺客信条阴影 - 如何找到铁匠，解锁武器和装甲定制
4 周前ByDDD
显示更多

热工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

SecLists

SecLists

SecLists是最终安全测试人员的伙伴。它是一个包含各种类型列表的集合，这些列表在安全评估过程中经常使用，都在一个地方。SecLists通过方便地提供安全测试人员可能需要的所有列表，帮助提高安全测试的效率和生产力。列表类型包括用户名、密码、URL、模糊测试有效载荷、敏感数据模式、Web shell等等。测试人员只需将此存储库拉到新的测试机上，他就可以访问到所需的每种类型的列表。

PhpStorm Mac 版本

PhpStorm Mac 版本

最新（2018.2.1 ）专业的PHP集成开发工具

Atom编辑器mac版下载

Atom编辑器mac版下载

最流行的的开源编辑器

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

功能强大的PHP集成开发环境

显示更多

热门话题

gmail邮箱登陆入口在哪里
7563
15
CakePHP 教程
1385
52
steam的账户名称是什么格式
84
11
win11激活密钥永久
61
19
NYT连接提示和答案
28
99
显示更多