SQL Server 游标运用:查看一个数据库所有表大小信息(Sizes of All Tables in a Database)

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: 原文:SQL Server 游标运用:查看一个数据库所有表大小信息(Sizes of All Tables in a Database)一.本文所涉及的内容(Contents) 本文所涉及的内容(Contents) 背景(Contexts) 实现代码(SQL Codes)方法一:运用游...
原文: SQL Server 游标运用:查看一个数据库所有表大小信息(Sizes of All Tables in a Database)

一.本文所涉及的内容(Contents)

  1. 本文所涉及的内容(Contents)
  2. 背景(Contexts)
  3. 实现代码(SQL Codes)
    1. 方法一:运用游标
    2. 方法二:运用系统存储过程
    3. 方法三:拼接SQL
  4. 参考文献(References)

二.背景(Contexts)

  在性能调优或者需要了解某数据库表信息的时候,最直观的方式就是罗列出这个数据所有表的信息,这些信息包括:表的记录数、数据记录占用空间、索引占用空间、未使用的空间等(如Figure1所示),有了这些信息你可以简单的判断这个数据库来自数据上的压力可能是某个表造成的。因为表数据越大,对数据库性能的影响越大。

  要实现某个数据库所有表的信息,可以通过游标的形式获取相应的数据,下图Figure1返回某数据库中所有表的信息:

image

(Figure1:某数据库所有表信息)

  也许你并不满足于Figure1的信息,你希望获取整个数据库实例中所有数据库所有表的信息(如Figure2所示),如果想了解里面的实现可以参考:SQL Server 查看所有数据库所有表大小信息(Sizes of All Tables in All Database)

F2

(Figure2:所有数据库所有表信息)

三.实现代码(SQL Codes)

为了实现Figure1的效果,有三种方式可以实现:

  1. 运用游标循环每个表,调用系统存储过程sp_spaceused把结果保存到临时表,可以过滤表;

  2. 运用系统存储过程sp_MSforeachtable把结果保存到临时表,可以过滤表;

  3. 运用系统表INFORMATION_SCHEMA.TABLES与系统存储过程sp_spaceused结合起来,拼接出每个表的INSERT语句,把结果保存到临时表,可以过滤表;

 

(一) 运用游标循环每个表,调用系统存储过程sp_spaceused把结果保存到临时表,可以过滤表;

  首先定义一个临时表变量@tablespaceinfo用于保存表的信息,使用游标读取sys.tables中的表名称,再通过sp_spaceused获取这个表的相关数据插入到临时表变量@tablespaceinfo。下面是SQL脚本的实现,效果就如Figure1所示:

--Script1:
--查看某数据库所有表的信息
DECLARE @tablespaceinfo TABLE (
    [name] SYSNAME,
    [rows] BIGINT,
    [reserved] VARCHAR(100),
    [data] VARCHAR(100),
    [index_size] VARCHAR(100),
    [unused] VARCHAR(100)
)

DECLARE @tablename VARCHAR(255);

DECLARE Info_cursor CURSOR FOR
    SELECT '['+[name]+']' FROM sys.tables WHERE TYPE='U';

OPEN Info_cursor
FETCH NEXT FROM Info_cursor INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @tablespaceinfo EXEC sp_spaceused @tablename
    
    FETCH NEXT FROM Info_cursor INTO @tablename
END

CLOSE Info_cursor
DEALLOCATE Info_cursor

SELECT * FROM @tablespaceinfo
    ORDER BY Cast(Replace(reserved,'KB','') AS INT) DESC

(二) 运用系统存储过程sp_MSforeachtable把结果保存到临时表,可以过滤表;

  下面Script2脚本是通过使用系统存储过程sp_MSforeachtable,返回的结果如Figure1所示:

--Script2:
--查看某数据库所有表的信息
DECLARE @tablespaceinfo TABLE (
    [name] SYSNAME,
    [rows] BIGINT,
    [reserved] VARCHAR(100),
    [data] VARCHAR(100),
    [index_size] VARCHAR(100),
    [unused] VARCHAR(100)
)

INSERT INTO @tablespaceinfo EXEC sp_MSforeachtable @command1="sp_spaceused '?'"

SELECT * FROM @tablespaceinfo
    ORDER BY Cast(Replace(reserved,'KB','') AS INT) DESC

  1) 如果你想使用sp_MSforeachtable但是又想过滤掉一些表,你可以在查询@tablespaceinfo的时候加入Where条件(这种方式就不演示了);或者直接在开始使用sp_MSforeachtable的时候就进行过滤:

--获取所有表信息
EXEC sp_MSforeachtable @command1="sp_spaceused '?'"

如果我们加入@whereand参数进行过滤,但是会出现下图的错误信息:

--过滤某些表
EXEC sp_MSforeachtable @whereand="and [name] like 't%'", @command1="sp_spaceused '?'"

clip_image001[1]

(Figure3:错误信息)

  经过查看sp_MSforeachtable的源代码,发现是有多个[name]字段的,所以指明字段,上面的错误就可以解决了,效果如下图所示。把下面的SQL代码替换Script2的部分代码就可以了。

--过滤某些表
EXEC sp_MSforeachtable @whereand="and o.[name] like 't%'", @command1="sp_spaceused '?'"
--或者
EXEC sp_MSforeachtable @whereand="and syso.[name] like 't%'", @command1="sp_spaceused '?'"

clip_image002[3]

(Figure4:过滤返回的结果)

  2) 在sp_MSforeachtable系统存储过程,有@whereand的参数可以对表进行过滤,sp_MSForEachDB系统存储是否也有同样的功能呢?查看了sp_MSForEachDB的源码,我们发现是没有这个功能的,为实现这个功能,我对sp_MSForEachDB进行修改,重新命名为:sp_MSForEachDB_Filter

--扩展sp_MSforeachdb
create proc dbo.[sp_MSforeachdb_Filter]
    @command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,
    @whereand nvarchar(2000) = null,@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
    set deadlock_priority low
    
    declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)
    select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
    select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
    select @dbinaccessible = N'0x80000000'        

    if (@precommand is not null)
        exec(@precommand)

    declare @origdb nvarchar(128)
    select @origdb = db_name()
    exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +
            N' where (d.status & ' + @inaccessible + N' = 0)' +
            N' and (DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1))' + @whereand)

    declare @retval int
    select @retval = @@error
    if (@retval = 0)
        exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1

    if (@retval = 0 and @postcommand is not null)
        exec(@postcommand)

   declare @tempdb nvarchar(258)
   SELECT @tempdb = REPLACE(@origdb, N']', N']]')
   exec (N'use ' + N'[' + @tempdb + N']')

    return @retval

  上面的存储过程sp_MSforeachdb_Filter是依照sp_MSforeachtable进行简单修改的,创建之后就可以使用下面的SQL脚本进行过滤数据库了,效果如下图所示:

--过滤数据库
EXEC [sp_MSforeachdb_Filter] @command1="print '?'",
@whereand=" and [name] not in('tempdb','master','model','msdb') "

clip_image003[1]

(Figure5:过滤数据库)

(三) 运用系统表INFORMATION_SCHEMA.TABLES与系统存储过程sp_spaceused结合起来,拼接出每个表的INSERT语句,把结果保存到临时表,可以过滤表;

  一次偶然的机会看到一个同样能实现Figure1效果的SQL脚本,它使用了系统表INFORMATION_SCHEMA.TABLES,下面是我修改过的SQL脚本,区别就在于可以满足对不同架构表的查询。原文详情可以参考:How to get information about all databases without a loop

--Script3:Sizes of All Tables in a Database
--exec sp_MSforeachtable 'print ''?'' exec sp_spaceused ''?'''
--在它的基础上做了些修改,适合不同的框架dbo等
IF OBJECT_ID('tempdb..#TablesSizes') IS NOT NULL
    DROP TABLE #TablesSizes

CREATE TABLE #TablesSizes (TableName sysname, Rows bigint, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100))

DECLARE @sql VARCHAR(MAX)
SELECT @sql = COALESCE(@sql,'') + '
INSERT INTO #TablesSizes execute sp_spaceused ''' + QUOTENAME(TABLE_SCHEMA,'[]') + '.' + QUOTENAME(Table_Name,'[]') + ''''
    FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

PRINT (@SQL)
EXECUTE (@SQL)

SELECT * FROM #TablesSizes ORDER BY TableName

 上面PRINT出来的脚本类似下图Figure6所示:

 

(Figure6:拼接的SQL代码)

四.参考文献(References)

与存储过程sp_MSforeachdb类似的存储过程sp_MSforeachdb

SQL Server数据库开发顶级技巧

sp_MSforeachtable使用方法

How to get information about all databases without a loop

关于quotename的用法

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
2天前
|
SQL 关系型数据库 MySQL
|
1天前
|
存储 SQL 关系型数据库
MySQL查询数据库锁表的SQL语句
MySQL查询数据库锁表的SQL语句
12 1
|
2天前
|
SQL 数据可视化 关系型数据库
【数据库工具】DBeaver:一款免费的通用数据库工具和 SQL 客户端
【数据库工具】DBeaver:一款免费的通用数据库工具和 SQL 客户端
10 1
|
2天前
|
SQL 关系型数据库 MySQL
sql注入原理与实战(三)数据库操作
sql注入原理与实战(三)数据库操作
sql注入原理与实战(三)数据库操作
|
9天前
|
SQL 存储 移动开发
HTML5 Web SQL 数据库详解
Web SQL 数据库是 HTML5 中的一种本地存储技术,允许在浏览器中使用 SQL 语言操作本地数据,支持离线访问和事务处理,适用于缓存数据和小型应用。然而,其存储容量有限且仅部分现代浏览器支持,标准已不再积极维护,未来可能被 IndexedDB 和 localStorage 等技术取代。使用时需谨慎考虑兼容性和发展前景。
|
1天前
|
SQL 数据库
SQL-serve数据库不能连接本地服务器的解决方案
SQL-serve数据库不能连接本地服务器的解决方案
22 0
|
2天前
|
SQL 存储 Java
sql注入原理与实战(二)数据库原理
sql注入原理与实战(二)数据库原理
|
25天前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择:MySQL、PostgreSQL 还是 NoSQL?
在微服务架构中,数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中,我们将深入探讨传统的关系型数据库(如 MySQL 和 PostgreSQL)与现代 NoSQL 数据库的优劣势,并分析在微服务架构下的最佳实践。
|
3天前
|
存储 关系型数据库 MySQL
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB区别,适用场景
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景比较
|
22天前
|
存储 SQL 关系型数据库
MySQL的安装&数据库的简单操作
本文介绍了数据库的基本概念及MySQL的安装配置。首先解释了数据库、数据库管理系统和SQL的概念,接着详细描述了MySQL的安装步骤及其全局配置文件my.ini的调整方法。文章还介绍了如何启动MySQL服务,包括配置环境变量和使用命令行的方法。最后,详细说明了数据库的各种操作,如创建、选择和删除数据库的SQL语句,并提供了实际操作示例。
62 13
MySQL的安装&数据库的简单操作

热门文章

最新文章