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