首页 > 数据库 >SQL Server数据库查看所有数据库的所有表的大小

SQL Server数据库查看所有数据库的所有表的大小

时间:2024-06-21 09:21:58浏览次数:20  
标签:NULL JOIN 数据库 id sys pages SQL Server SUM

1. 查看单个库所有表大小

SELECT
	DatabaseName = db_name(),
	SchemaName = sch.name,
	TableName = tab.name,
	TotalRowCount = par.rows,
	TotalSpace = SUM(alc.total_pages) * 8,
	UsedSpace = SUM(alc.used_pages) * 8,
	UnusedSpace = (SUM(alc.total_pages) - SUM(alc.used_pages)) * 8
FROM .sys.tables tab
INNER JOIN .sys.indexes ind ON tab.object_id = ind.object_id
INNER JOIN .sys.partitions par  ON ind.object_id = par.object_id AND ind.index_id = par.index_id
INNER JOIN .sys.allocation_units alc ON par.partition_id = alc.container_id
LEFT OUTER JOIN .sys.schemas sch ON tab.schema_id = sch.schema_id
GROUP BY
   tab.name,
   sch.name,
   par.rows;

2. 查看所有数据库的所有表的大小--格式不整齐

DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'',''tactical'', ''tactical_etf_testing'',
''ReportServer'',''tactical_master'', ''tactical_performance'', ''ReportServerTempDB'', ''jydb'', ''ops_dw'') BEGIN USE ? EXEC(''
SELECT
	db_name() as DatabaseName,
	s.Name AS SchemaName,
	t.NAME AS TableName,
	p.rows AS RowCounts,
	CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
	CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
	CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
	sys.tables t
INNER JOIN      
	sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
	sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
	sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
	sys.schemas s ON t.schema_id = s.schema_id
GROUP BY
   s.Name, t.Name,  p.Rows
'') END'
EXEC sp_MSforeachdb @command

3. 查看所有数据库的所有表的大小--格式整齐,单位换算

IF OBJECT_ID('tempdb..#tablespaceinfo') IS NOT NULL
BEGIN
	drop table #tablespaceinfo
END

CREATE TABLE #tablespaceinfo(
DatabaseName [sysname] NULL,
SchemaName [sysname] NULL,
TableName [sysname] NULL,
RowCounts [bigint] NULL,
TotalSpaceMB [varchar](100) NULL,
UsedSpaceMB [varchar](100) NULL,
UnusedSpaceMB [varchar](100) NULL
)

DECLARE @SQL NVARCHAR(MAX);
SET @SQL ='IF ''?'' not in(''master'', ''model'', ''msdb'', ''tempdb'',''tactical'', ''tactical_etf_testing'',''ReportServer'',
''tactical_master'', ''tactical_performance'', ''ReportServerTempDB'', ''jydb'', ''ops_dw'')
BEGIN
	use ?
	EXEC(
		''insert into #tablespaceinfo
		SELECT
		db_name() as DatabaseName,
		s.Name AS SchemaName,
		t.NAME AS TableName,
		p.rows AS RowCounts,
		CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
		CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
		CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
		FROM sys.tables t
		INNER JOIN      
			sys.indexes i ON t.OBJECT_ID = i.object_id
		INNER JOIN
			sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
		INNER JOIN
			sys.allocation_units a ON p.partition_id = a.container_id
		LEFT OUTER JOIN
			sys.schemas s ON t.schema_id = s.schema_id
		GROUP BY
		   s.Name, t.Name,  p.Rows
	'')
END'

PRINT @SQL

EXEC master..sp_MSForEachDB @SQL
select * from #tablespaceinfo;

drop table #tablespaceinfo;

4. 查看所有数据库的所有表的大小--单位没有换算

IF OBJECT_ID('tempdb..#tablespaceinfo') IS NOT NULL
BEGIN
	drop table #tablespaceinfo
END

CREATE TABLE #tablespaceinfo(
DatabaseName [sysname] NULL,
SchemaName [sysname] NULL,
TableName [sysname] NULL,
TotalRowCount [bigint] NULL,
TotalSpace [varchar](100) NULL,
UsedSpace [varchar](100) NULL,
UnusedSpace [varchar](100) NULL
)

DECLARE @SQL NVARCHAR(MAX);
SET @SQL ='IF ''?'' not in(''master'', ''model'', ''msdb'', ''tempdb'',''tactical'', ''tactical_etf_testing'',''ReportServer'',
''tactical_master'', ''tactical_performance'', ''ReportServerTempDB'', ''jydb'', ''ops_dw'')
BEGIN
	use ?
	EXEC(
		''insert into #tablespaceinfo
		SELECT
		DatabaseName = db_name(),
		SchemaName = sch.name,
		TableName = tab.name,
		TotalRowCount = par.rows,
		TotalSpace = SUM(alc.total_pages) * 8,
		UsedSpace = SUM(alc.used_pages) * 8,
		UnusedSpace = (SUM(alc.total_pages) - SUM(alc.used_pages)) * 8
		FROM .sys.tables tab
		INNER JOIN .sys.indexes ind ON tab.object_id = ind.object_id
		INNER JOIN .sys.partitions par  ON ind.object_id = par.object_id AND ind.index_id = par.index_id
		INNER JOIN .sys.allocation_units alc ON par.partition_id = alc.container_id
		LEFT OUTER JOIN .sys.schemas sch ON tab.schema_id = sch.schema_id
		GROUP BY tab.name,sch.name,par.rows
	'')
END'

PRINT @SQL

EXEC master..sp_MSForEachDB @SQL
select * from #tablespaceinfo;

drop table #tablespaceinfo;

标签:NULL,JOIN,数据库,id,sys,pages,SQL,Server,SUM
From: https://www.cnblogs.com/dber-ablewang/p/18259895

相关文章

  • windows安装MySQL
    windows安装MySQL1.下载MySQL的安装包这里以mysql5.7.35为例进行安装演示用安装包放在下面mysql-5.7.35-winx64.zip2.解压安装包到需要安装的路径3.在解压的文件中创建my.ini[mysqld]basedir=MySQL路径\datadir=MySQL路径\data\port=3306skip-grant-tables4.安装My......
  • keepalived实现Mysql的双机热备自动故障切换,看这一篇就够了!
    目录一、什么是双热备份?二、什么是Mysql的双热备份? 三、什么是keepalived?四、实现Mysql的双机热备1、配置双主复制参数2、创建用于复制的MySQL用户3、将A节点的数据拷贝到B节点4、B节点上开启复制五、 安装配置keepalived,完成故障自动切换1、keepalived的安装2、......
  • SqlserverCDCSourceSink
    此处调用官方sinkdemo,更新和删除逻辑还需要再判断实现。importcom.ververica.cdc.connectors.base.options.StartupOptions;importcom.ververica.cdc.connectors.sqlserver.SqlServerSource;importcom.ververica.cdc.connectors.sqlserver.source.SqlServerSourceBuilder;......
  • 常见数据库url 和driver
    转自:http://blog.csdn.net/kimsoft/article/details/3305675PropertiesfilewithJDBC-relatedsettings.##########HSQLDB##########jdbc.driverClassName=org.hsqldb.jdbcDriverjdbc.url=jdbc:hsqldb:hsql://localhost:9001/bookstorejdbc.username=sajdbc.password......
  • Uipath 如何连接SQLite数据进行操作
      1、下载SQLiteDatabaseActivitiesforUipathpackages,无需安装SQLite数据库驱动。2、SQLite查询操作   2.1添加查询活动:在UIPathStudio的“Activities”面板中,搜索并添加“ExecuteQuery”活动。这个活动用于执行SQL查询语。在“ExecuteQuery”活动的属......
  • 第20篇:Milvus与数据库系统的比较与整合
    随着大数据和人工智能技术的迅猛发展,数据库技术也在不断进化。关系型数据库(RDBMS)作为传统的数据管理工具,已经在数据存储和处理领域占据了重要地位。而近年来,向量数据库(如Milvus)专为处理高维向量数据而设计,特别适用于大规模、高维数据的相似性搜索和分析。本文将详细比较Milv......
  • 基于Java的图书管理系统源码( GUI+JavaSwing+MySql )
    基于Java的图书管理系统源码(GUI+JavaSwing+MySql)1、使用JavaSwing,Mysql实现2、IDEA编写,eclipse也可以运行(测试使用的IDEA24.1.3)3、测试环境:JDK1.8(JDK20),MySQL5.54、本项目实现了注册、登录、图书馆数据维护、新书订购、借阅管理、系统维护等功能=============......
  • MySQL高级SQL语句
    目录一.准备两个表二.高级查询方式1.select2.distinct3.where4.andor5.in6.between7.通配符8.like三.运用函数查询1.常用数学函数2.聚合函数3.字符串函数四.高级查询函数1.orderby2.groupby3.having4.别名设置查询4.1.字段别名4.2.表别名5.子查询语句......
  • MySQL中设置两个默认时间(createTime字段和updateTime字段)
    MySQL中设置两个默认时间在MySQL中,您可以使用DATETIME数据类型并设置默认值为CURRENT_TIMESTAMP来实现这一点。以下是一个示例,展示了如何为createTime和updateTime字段设置默认值:CREATETABLEtable_name(idINTPRIMARYKEY,createTimeDATETIMEDEFAULTCURRENT......
  • MySQl配置环境变量
    配置环境变量(a)添加一个系统变量,变量名:CATALINA-HOME,变量值:MySql在自己电脑当中的安装路径,注意:5.7版本需要配置的路径是MySQL.Sever5.7的文件夹路径。 (b)在Path变量的结尾添加一个英文分号,之后把上面添加的路径导入进去(%CATALINA-HOME%)在这个结尾处添加\bin.......