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

原创

dber_ablewang 2024-08-30 16:07:13 博主文章分类：SQL Server ©著作权

©著作权归作者所有：来自51CTO博客作者dber_ablewang的原创作品，请联系作者获取转载授权，否则将追究法律责任

查看单个库的所有表大小

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;

查看所有库的所有表的大小-格式不整齐

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

查看所有库的所有表的大小-格式整齐，已经做过单位换算

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;

查看所有库的所有表达大小-单位没有换算

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;