- 查看单个库的所有表大小
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;
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 查看所有库的所有表的大小-格式不整齐
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
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 查看所有库的所有表的大小-格式整齐,已经做过单位换算
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;
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 查看所有库的所有表达大小-单位没有换算
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;
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.