列出所有数据库上所有表的表大小

如果您想在您的所有环境中为您的所有数据库获得此功能...并且您不介意使用 PowerShell...您将需要从至少安装了 SQL Server 2008 Management Studio 的机器上运行它。

# Load SMO
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

function Get-TableSize ([string[]]$server) {
    foreach ($srv in $server) {
        $s = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $srv

        $s.Databases.Tables | 
            ? {-Not $_.IsSystemObject} | 
                Select @{Label="Server";Expression={$srv}},
                    @{Label="DatabaseName";Expression={$_.Parent}}, 
                    @{Label="TableName";Expression={$_.Name}}, 
                    @{Label="SizeKB";Expression={$_.DataSpaceUsed}}
    }
}

如标记为DataSpaceUsed“KB”的 SMO 对象输出，您可以将其修改为您选择的度量，只需为其添加缩写参考即可。所以如果我想要 "MB": $_.DataSpaceUsed/1MB。

在函数([string[]]$server)中，括号“[]”表示参数接受一个对象数组。因此，如果您在文件中列出了服务器，则可以像这样调用该函数：

$list = get-content .\ServerList.txt
Get-TableSize -server $list | Out-GridView

我更喜欢Out-GridView最初使用来查看输出，它可以轻松地直接复制到 Excel 中。如果需要，您还可以将其输出到其他受支持的 PowerShell 格式。

以屏幕截图为例，您也可以只列出服务器：

取自 Stack-Overflow：获取数据库中所有表的大小

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

我使用了以前答案的合并：

USE [master];
GO

sp_msforeachdb 'USE [?]; 
SELECT  
''?'' as db,    
t.NAME AS TableName,    
s.Name AS SchemaName,    
p.rows AS RowCounts,    
SUM(a.total_pages) * 8 AS TotalSpaceKB,     
SUM(a.used_pages) * 8 AS UsedSpaceKB, 
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB 
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 
WHERE    p.rows > 0 AND t.is_ms_shipped = 0    AND i.OBJECT_ID > 255 
GROUP BY     t.Name, s.Name, p.Rows 
ORDER BY p.rows DESC' ;

但是，您可以尝试使用sp_msforeachdb一些警告。

• 您正在使用动态 SQL，所以它可能有点棘手
• 它没有记录且不受支持，因此可能会在任何版本中更改或消失
• 它有一些已知的缺陷，包括快照问题和跳过奇数数据库

话虽如此，我已经成功使用了几年。

sp_msforeachdb 'USE [?]; SELECT * FROM sys.tables'

基本上它在 ? 与数据库名称。

您也可以尝试 Aaron Bertrand 的替换版本。 我自己没有尝试过，但应该会更好。

以下将解决您的问题：

use master
DECLARE @xQry NVARCHAR(MAX)=''
SELECT @xQry+= ' UNION ALL SELECT '''+name+''' COLLATE Modern_Spanish_CI_AS AS [Database], 
    schema_name(tab.schema_id) + ''.'' + tab.name COLLATE Modern_Spanish_CI_AS AS [table], 
        cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
        cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
    from '+name+'.sys.tables tab
    join '+name+'.sys.indexes ind 
         on tab.object_id = ind.object_id
    join '+name+'.sys.partitions part 
         on ind.object_id = part.object_id and ind.index_id = part.index_id
    join '+name+'.sys.allocation_units spc
         on part.partition_id = spc.container_id
    group by schema_name(tab.schema_id) + ''.'' + tab.name COLLATE Modern_Spanish_CI_AS'
FROM sys.databases 

SET @xQry= RIGHT(@xQry,LEN(@xQry)-11) + ' order by 3 desc'
EXEC (@xQry)

类似于 Fer R 的答案，但合并为一个结果集。与 sp_MSforeachdb 相关的警告适用。

set nocount on

USE [master];
GO

if object_id('tempdb..#temp') is not null drop table #temp

CREATE TABLE #temp(
    rec_id      int IDENTITY (1, 1),
    db sysname, 
    TableName   varchar(128),
    SchemaName varchar(128),
    RowCounts   bigint,
    TotalSpaceMB    decimal(15,2),  
    UsedSpaceMB decimal(15,2),
    UnusedSpaceMB   decimal(15,2)   )


exec sp_MSforeachdb 'USE [?]; 
insert into #temp (db, TableName, SchemaName, RowCounts, TotalSpaceMB, UsedSpaceMB, UnusedSpaceMB)
SELECT  
''?'' as db,    
t.NAME AS TableName,    
s.Name AS SchemaName,    
p.rows AS RowCounts,    
SUM(a.total_pages) * 8/1024 AS TotalSpaceMB,     
SUM(a.used_pages) * 8/1024 AS UsedSpaceMB, 
(SUM(a.total_pages) - SUM(a.used_pages)) * 8/1024 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 
WHERE    p.rows > 0 AND t.is_ms_shipped = 0    AND i.OBJECT_ID > 255 
GROUP BY     t.Name, s.Name, p.Rows 
ORDER BY p.rows DESC' ;

select db, TableName, SchemaName, RowCounts, TotalSpaceMB, UsedSpaceMB, UnusedSpaceMB
from #temp
order by db, SchemaName, TableName