获取数据库中所有表的大小

我继承了一个相当大的SQL服务器数据库。鉴于它包含的数据，它似乎比我预期的占用更多的空间。

有没有一种简单的方法来确定每个表占用多少磁盘空间？

1891067 次浏览
小开
最佳答案 
SELECTt.NAME AS TableName,s.Name AS SchemaName,p.rows,SUM(a.total_pages) * 8 AS TotalSpaceKB,CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,SUM(a.used_pages) * 8 AS UsedSpaceKB,CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMBFROMsys.tables tINNER JOINsys.indexes i ON t.OBJECT_ID = i.object_idINNER JOINsys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_idINNER JOINsys.allocation_units a ON p.partition_id = a.container_idLEFT OUTER JOINsys.schemas s ON t.schema_id = s.schema_idWHEREt.NAME NOT LIKE 'dt%'AND t.is_ms_shipped = 0AND i.OBJECT_ID > 255GROUP BYt.Name, s.Name, p.RowsORDER BYTotalSpaceMB DESC, t.Name
小开

经过一番搜索，我找不到一种简单的方法来获取所有表的信息。有一个名为sp_spaceused的方便存储过程，它将返回数据库使用的所有空间。如果提供了表名，它将返回该表使用的空间。然而，存储过程返回的结果不可排序，因为列是字符值。

以下脚本将生成我要查找的信息。

create table #TableSize (Name varchar(255),[rows] int,reserved varchar(255),data varchar(255),index_size varchar(255),unused varchar(255))create table #ConvertedSizes (Name varchar(255),[rows] int,reservedKb int,dataKb int,reservedIndexSize int,reservedUnused int)
EXEC sp_MSforeachtable @command1="insert into #TableSizeEXEC sp_spaceused '?'"insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused)select name, [rows],SUBSTRING(reserved, 0, LEN(reserved)-2),SUBSTRING(data, 0, LEN(data)-2),SUBSTRING(index_size, 0, LEN(index_size)-2),SUBSTRING(unused, 0, LEN(unused)-2)from #TableSize
select * from #ConvertedSizesorder by reservedKb desc
drop table #TableSizedrop table #ConvertedSizes
小开 
 exec  sp_spaceused N'dbo.MyTable'

对于所有表格，使用…（从Paul的评论中添加）

exec sp_MSForEachTable 'exec sp_spaceused [?]'
小开

这将为您提供每个表的大小和记录计数。

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO-- Get a list of tables and their sizes on diskALTER PROCEDURE [dbo].[sp_Table_Sizes]ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;DECLARE @table_name VARCHAR(500)DECLARE @schema_name VARCHAR(500)DECLARE @tab1 TABLE(tablename VARCHAR (500) collate database_default,schemaname VARCHAR(500) collate database_default)
CREATE TABLE #temp_Table (tablename sysname,row_count INT,reserved VARCHAR(50) collate database_default,data VARCHAR(50) collate database_default,index_size VARCHAR(50) collate database_default,unused VARCHAR(50) collate database_default)
INSERT INTO @tab1SELECT Table_Name, Table_SchemaFROM information_schema.tablesWHERE TABLE_TYPE = 'BASE TABLE'
DECLARE c1 CURSOR FORSELECT Table_Schema + '.' + Table_NameFROM information_schema.tables t1WHERE TABLE_TYPE = 'BASE TABLE'
OPEN c1FETCH NEXT FROM c1 INTO @table_nameWHILE @@FETCH_STATUS = 0BEGINSET @table_name = REPLACE(@table_name, '[','');SET @table_name = REPLACE(@table_name, ']','');
-- make sure the object exists before calling sp_spacedusedIF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name))BEGININSERT INTO #temp_Table EXEC sp_spaceused @table_name, false;END
FETCH NEXT FROM c1 INTO @table_nameENDCLOSE c1DEALLOCATE c1
SELECT  t1.*,t2.schemanameFROM #temp_Table t1INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )ORDER BY schemaname,t1.tablename;
DROP TABLE #temp_TableEND
小开

sp_spaceused可以获取有关表、索引视图或整个数据库使用的磁盘空间的信息。

例如：

USE MyDatabase; GO
EXEC sp_spaceused N'User.ContactInfo'; GO

这将报告ContttInfo表的磁盘使用情况信息。

要同时对所有表使用它：

USE MyDatabase; GO
sp_msforeachtable 'EXEC sp_spaceused [?]' GO

您还可以从右键单击SQLServer的标准报表功能中获取磁盘使用情况。要获取此报表，请从对象资源管理器中的服务器对象导航，向下移动到数据库对象，然后右键单击任何数据库。从出现的菜单中，依次选择报表，然后选择标准报表，然后选择“按分区划分的磁盘使用情况：[数据库名称]”。

小开

我的文章仅适用于SQLServer 2000，并已在我的环境中进行了测试。

这段代码访问单个实例的所有可能数据库，而不仅仅是一个数据库。

我使用两个临时表来帮助收集适当的数据，然后将结果转储到一个“Live”表中。

返回的数据是：数据库名称、数据库表名称、行（在表中）、数据（表的大小似乎是KB）、条目数据（我发现这有助于了解我上次运行脚本的时间）。

此代码的缺点是“data”字段未存储为int（字符“KB”保留在该字段中），这对于排序很有用（但并非完全必要）。

希望这段代码能帮助到外面的人，为他们节省一些时间！

CREATE PROCEDURE [dbo].[usp_getAllDBTableSizes]
ASBEGINSET NOCOUNT OFF
CREATE TABLE #DatabaseTables([dbname] sysname,TableName sysname)CREATE TABLE #AllDatabaseTableSizes(Name sysname,[rows] VARCHAR(18), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))
DECLARE @SQL nvarchar(4000)SET @SQL='select ''?'' AS [Database], Table_Name from [?].information_schema.tables WHERE TABLE_TYPE = ''BASE TABLE'' '
INSERT INTO #DatabaseTables(DbName, TableName)EXECUTE sp_msforeachdb @Command1=@SQL
DECLARE AllDatabaseTables CURSOR LOCAL READ_ONLY FORSELECT TableName FROM #DatabaseTables
DECLARE AllDatabaseNames CURSOR LOCAL READ_ONLY FORSELECT DBName FROM #DatabaseTables
DECLARE @DBName sysnameOPEN AllDatabaseNames
DECLARE @TName sysnameOPEN AllDatabaseTables
WHILE 1=1 BEGINFETCH NEXT FROM AllDatabaseNames INTO @DBNameFETCH NEXT FROM AllDatabaseTables INTO @TNameIF @@FETCH_STATUS<>0 BREAKINSERT INTO #AllDatabaseTableSizesEXEC ( 'EXEC ' + @DBName + '.dbo.sp_spaceused ' + @TName)
END
--http://msdn.microsoft.com/en-us/library/aa175920(v=sql.80).aspxINSERT INTO rsp_DatabaseTableSizes (DatabaseName, name, [rows], data)SELECT   [dbname], name, [rows],  data FROM #DatabaseTablesINNER JOIN #AllDatabaseTableSizesON #DatabaseTables.TableName = #AllDatabaseTableSizes.NameGROUP BY [dbname] , name, [rows],  dataORDER BY [dbname]--To be honest, I have no idea what exact duplicates we are dropping-- but in my case a near enough approach has been good enough.DELETE FROM [rsp_DatabaseTableSizes]WHERE name IN(SELECT nameFROM [rsp_DatabaseTableSizes]GROUP BY nameHAVING COUNT(*) > 1)
DROP TABLE #DatabaseTablesDROP TABLE #AllDatabaseTableSizes
CLOSE AllDatabaseTablesDEALLOCATE AllDatabaseTables
CLOSE AllDatabaseNamesDEALLOCATE AllDatabaseNamesEND
--EXEC [dbo].[usp_getAllDBTableSizes]

如果您需要知道，rsp_DatabaseTableSizes表是通过以下方式创建的：

CREATE TABLE [dbo].[rsp_DatabaseSizes]([DatabaseName] [varchar](1000) NULL,[dbSize] [decimal](15, 2) NULL,[DateUpdated] [smalldatetime] NULL) ON [PRIMARY]
GO
小开

如果您使用的是SQL服务器管理工作室（SSMS），而不是运行查询（在我的情况下返回重复行），您可以运行标准报告。

  1. 右键单击数据库
  2. 导航到报告>标准报告>按表划分的磁盘使用情况

注意：数据库兼容性级别必须设置为90或更高才能正常工作。请参阅http://msdn.microsoft.com/en-gb/library/bb510680.aspx

小开

上述查询有助于查找表（包括索引）使用的空间量，但如果您想比较表上索引使用的空间量，请使用此查询：

SELECTOBJECT_NAME(i.OBJECT_ID) AS TableName,i.name AS IndexName,i.index_id AS IndexID,8 * SUM(a.used_pages) AS 'Indexsize(KB)'FROMsys.indexes AS iJOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_idJOIN sys.allocation_units AS a ON a.container_id = p.partition_idWHEREi.is_primary_key = 0 -- fix for size discrepancyGROUP BYi.OBJECT_ID,i.index_id,i.nameORDER BYOBJECT_NAME(i.OBJECT_ID),i.index_id
小开

我在答案的顶部添加了marc_s列：

with fsas(select i.object_id,p.rows AS RowCounts,SUM(a.total_pages) * 8 AS TotalSpaceKbfrom     sys.indexes i INNER JOINsys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOINsys.allocation_units a ON p.partition_id = a.container_idWHEREi.OBJECT_ID > 255GROUP BYi.object_id,p.rows)
SELECTt.NAME AS TableName,fs.RowCounts,fs.TotalSpaceKb,t.create_date,t.modify_date,( select COUNT(1)from sys.columns cwhere c.object_id = t.object_id ) TotalColumnsFROMsys.tables t INNER JOINfs  ON t.OBJECT_ID = fs.object_idWHEREt.NAME NOT LIKE 'dt%'AND t.is_ms_shipped = 0ORDER BYt.Name
小开 
-- Show the size of all the tables in a database sort by data size descendingSET NOCOUNT ONDECLARE @TableInfo TABLE (tablename varchar(255), rowcounts int, reserved varchar(255), DATA varchar(255), index_size varchar(255), unused varchar(255))DECLARE @cmd1 varchar(500)SET @cmd1 = 'exec sp_spaceused ''?'''
INSERT INTO @TableInfo (tablename,rowcounts,reserved,DATA,index_size,unused)EXEC sp_msforeachtable @command1=@cmd1
SELECT * FROM @TableInfo ORDER BY Convert(int,Replace(DATA,' KB','')) DESC
小开

如果你需要计算完全相同的数字，在SSMS中的“表属性-存储”页面上，你需要使用与SSMS相同的方法来计算它们（适用于sql server 2005及更高版本……并且也适用于具有LOB字段的表-因为仅仅计算“used_pages”不足以显示准确的索引大小）：

;with cte as (SELECTt.name as TableName,SUM (s.used_page_count) as used_pages_count,SUM (CASEWHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)ELSE lob_used_page_count + row_overflow_used_page_countEND) as pagesFROM sys.dm_db_partition_stats  AS sJOIN sys.tables AS t ON s.object_id = t.object_idJOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_idGROUP BY t.name)selectcte.TableName,cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB,cast(((CASE WHEN cte.used_pages_count > cte.pagesTHEN cte.used_pages_count - cte.pagesELSE 0END) * 8./1024) as decimal(10,3)) as IndexSizeInMBfrom cteorder by 2 desc
小开

从使用OSQL的命令提示符：

OSQL -E -d <*databasename*> -Q "exec sp_msforeachtable 'sp_spaceused [?]'" > result.txt
小开

要在一个数据库中获取所有表大小，您可以使用此查询：

Exec sys.sp_MSforeachtable ' sp_spaceused "?" '

您可以更改它以将所有结果插入临时表，然后从临时表中选择。

Insert into #TempTable Exec sys.sp_MSforeachtable ' sp_spaceused "?" 'Select * from #TempTable
小开

在上面的@Mark回答中，添加了@updateuse='true'以强制最新的尺寸统计（https://msdn.microsoft.com/en-us/library/ms188776.aspx）：

        SET NOCOUNT ONDECLARE @TableInfo TABLE (tablename varchar(255), rowcounts int, reserved varchar(255), DATA varchar(255), index_size varchar(255), unused varchar(255))DECLARE @cmd1 varchar(500)SET @cmd1 = 'exec sp_spaceused @objname =''?'', @updateusage =''true'' '
INSERT INTO @TableInfo (tablename,rowcounts,reserved,DATA,index_size,unused)EXEC sp_msforeachtable @command1=@cmd1SELECT * FROM @TableInfo ORDER BY Convert(int,Replace(DATA,' KB','')) DESC
小开

从Marc_s的回答开始的一个小变化，因为我经常回到这个页面，按大多数行的第一个排序：

SELECTt.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 UnusedSpaceKBFROMsys.tables tINNER JOINsys.indexes i ON t.OBJECT_ID = i.object_idINNER JOINsys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_idINNER JOINsys.allocation_units a ON p.partition_id = a.container_idLEFT OUTER JOINsys.schemas s ON t.schema_id = s.schema_idWHEREt.NAME NOT LIKE 'dt%'AND t.is_ms_shipped = 0AND i.OBJECT_ID > 255GROUP BYt.Name, s.Name, p.RowsORDER BY--p.rows DESC --Uncomment to order by amount rows instead of size in KB.SUM(a.total_pages) DESC
小开

我们使用表分区，并且由于重复记录而在上面提供的查询中遇到了一些麻烦。

对于那些需要这个的人，您可以在下面找到SQLServer 2014在生成“表磁盘使用情况”报告时运行的查询。我假设它也适用于以前版本的SQLServer。

它像一个魅力。

SELECTa2.name AS [tablename],a1.rows as row_count,(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,a1.data * 8 AS data,(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unusedFROM(SELECTps.object_id,SUM (CASEWHEN (ps.index_id < 2) THEN row_countELSE 0END) AS [rows],SUM (ps.reserved_page_count) AS reserved,SUM (CASEWHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)END) AS data,SUM (ps.used_page_count) AS usedFROM sys.dm_db_partition_stats psWHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)GROUP BY ps.object_id) AS a1LEFT OUTER JOIN(SELECTit.parent_id,SUM(ps.reserved_page_count) AS reserved,SUM(ps.used_page_count) AS usedFROM sys.dm_db_partition_stats psINNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)WHERE it.internal_type IN (202,204)GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)WHERE a2.type <> N'S' and a2.type <> N'IT'ORDER BY a3.name, a2.name
小开

以下是通过以下步骤快速获取所有表大小的方法：

  1. 编写给定的T-SQL命令以列出所有数据库表：

    select 'exec sp_spaceused ' + TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'

  2. Now copy the list of database tables, and copy it into a new query analyzer window

    exec sp_spaceused table1exec sp_spaceused table2exec sp_spaceused table3exec sp_spaceused table4exec sp_spaceused table5

  3. In SQL query analyzer, select from top tool bar option Results to file (Ctrl + Shift + F).

  4. Now finally hit the Execute button red marked from the above tool bar.

  5. The Database size of all tables is now stored in a file on your computer.

    Enter image description here

小开

作为marc_s的回答（已接受的）的简单扩展，这被调整为返回列计数并允许过滤：

SELECT *FROM(
SELECTt.NAME AS TableName,s.Name AS SchemaName,p.rows AS RowCounts,COUNT(DISTINCT c.COLUMN_NAME) as ColumnCount,SUM(a.total_pages) * 8 AS TotalSpaceKB,(SUM(a.used_pages) * 8) AS UsedSpaceKB,(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKBFROMsys.tables tINNER JOINsys.indexes i ON t.OBJECT_ID = i.object_idINNER JOINsys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_idINNER JOINsys.allocation_units a ON p.partition_id = a.container_idINNER JOININFORMATION_SCHEMA.COLUMNS c ON t.NAME = c.TABLE_NAMELEFT OUTER JOINsys.schemas s ON t.schema_id = s.schema_idWHEREt.NAME NOT LIKE 'dt%'AND t.is_ms_shipped = 0AND i.OBJECT_ID > 255GROUP BYt.Name, s.Name, p.Rows) AS Result
WHERERowCounts > 1000AND ColumnCount > 10ORDER BYUsedSpaceKB DESC
小开

这是另一种方法：使用SQL服务器管理工作室，在对象资源管理器中，转到您的数据库并选择表格

在此处输入图片描述

然后打开对象资源管理器详细信息（按F7或转到查看->对象资源管理器详细信息）。在对象资源管理器详情页中，右键单击列标题并启用您希望在页面中看到的列。您也可以按任何列对数据进行排序。

在此处输入图片描述

小开

扩展到@xav回答，它处理表分区以获取MB和GB的大小。在SQLServer 2008/2012上测试（注释了is_memory_optimized = 1的一行）

SELECTa2.name AS TableName,a1.rows as [RowCount],--(a1.reserved + ISNULL(a4.reserved,0)) * 8 AS ReservedSize_KB,--a1.data * 8 AS DataSize_KB,--(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS IndexSize_KB,--(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS UnusedSize_KB,CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_MB,CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_MB,CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_MB,CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_MB,--'| |' Separator_MB_GB,CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_GB,CAST(ROUND(a1.data * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_GB,CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_GB,CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_GBFROM(SELECTps.object_id,SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],SUM (ps.reserved_page_count) AS reserved,SUM (CASEWHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)END) AS data,SUM (ps.used_page_count) AS usedFROM sys.dm_db_partition_stats ps--===Remove the following comment for SQL Server 2014+--WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)GROUP BY ps.object_id) AS a1LEFT OUTER JOIN(SELECTit.parent_id,SUM(ps.reserved_page_count) AS reserved,SUM(ps.used_page_count) AS usedFROM sys.dm_db_partition_stats psINNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)WHERE it.internal_type IN (202,204)GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)WHERE a2.type <> N'S' and a2.type <> N'IT'--AND a2.name = 'MyTable'       --Filter for specific table--ORDER BY a3.name, a2.nameORDER BY ReservedSize_MB DESC
小开

这是一个示例查询，用于获取按大小降序排序的大于1GB的表。

USE YourDBGO
DECLARE @Mult float = 8SET @Mult = @Mult / POWER(2, 20) -- Use POWER(2, 10) for MBs
; WITH CTE AS(SELECTi.object_id,Rows = MAX(p.rows),TotalSpaceGB = ROUND(SUM(a.total_pages) * @Mult, 0),UsedSpaceGB = ROUND(SUM(a.used_pages) * @Mult, 0)FROMsys.indexes iJOINsys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_idJOINsys.allocation_units a ON p.partition_id = a.container_idWHEREi.object_id > 255GROUP BYi.object_idHAVINGSUM(a.total_pages) * @Mult > 1)SELECTSchemaName = s.name,TableName = t.name,c.TotalSpaceGB,c.UsedSpaceGB,UnusedSpaceGB = c.TotalSpaceGB - c.UsedSpaceGB,[RowCount] = c.RowsFROMCTE cJOINsys.tables t ON t.object_id = c.object_idJOINsys.schemas s ON t.schema_id = s.schema_idORDER BYc.TotalSpaceGB DESC
小开

对于Azure，我使用了这个：

您应该有SSMS v17. x+

我使用；

在此处输入图片描述

因此，作为用户斯派洛提到：

打开Databases>并选择表格，
然后按F7键你应该看看row count
作为：输入图片描述

SSMS这里连接到Azure数据库

小开

我发现这个查询很容易使用和快速。

select schema_name(tab.schema_id) + '.' + tab.name 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_mbfrom sys.tables (nolock) tabinner join sys.indexes (nolock) indon tab.object_id = ind.object_idinner join sys.partitions  (nolock) parton ind.object_id = part.object_id and ind.index_id = part.index_idinner join sys.allocation_units (nolock) spcon part.partition_id = spc.container_idgroup by schema_name(tab.schema_id) + '.' + tab.nameorder by sum(spc.used_pages) desc
小开

如果您只关心数据库中的空浪费空间而不是单个表，您可以考虑以下几点：

如果数据库经历了大量的数据插入然后删除，可能就像在ETL情况下一样，这将导致数据库中太多未使用的空间，因为文件组自动增长但永远不会自动收缩。

你可以使用数据库的属性页面来查看情况是否如此。你可以收缩（右键单击数据库>任务>收缩）并收回一些空间。但是，如果潜在的原因仍然存在，数据库会恢复增长（并消耗额外的时间试图恢复增长，并且速度会减慢，直到它增长足够-所以在这种情况下不要这样做）

（KEK：密钥加密密钥）

小开

Marc_s的回答在处理多个分区和/或过滤索引时给出了不正确结果。它也没有区分数据和索引的大小，这通常是非常相关的。一些建议的修复没有解决核心问题，或者只是错误的。

以下查询解决了所有这些问题。

SELECT[object_id]        = t.[object_id],[schema_name]      = s.[name],[table_name]       = t.[name],[index_name]       = CASE WHEN i.[type] in (0,1,5) THEN null    ELSE i.[name] END -- 0=Heap; 1=Clustered; 5=Clustered Columnstore,[object_type]      = CASE WHEN i.[type] in (0,1,5) THEN 'TABLE' ELSE 'INDEX'  END,[index_type]       = i.[type_desc],[partition_count]  = p.partition_count,[row_count]        = p.[rows],[data_compression] = CASE WHEN p.data_compression_cnt > 1 THEN 'Mixed'ELSE (  SELECT DISTINCT p.data_compression_descFROM sys.partitions pWHERE i.[object_id] = p.[object_id] AND i.index_id = p.index_id)END,[total_space_MB]   = cast(round(( au.total_pages                  * (8/1024.00)), 2) AS DECIMAL(36,2)),[used_space_MB]    = cast(round(( au.used_pages                   * (8/1024.00)), 2) AS DECIMAL(36,2)),[unused_space_MB]  = cast(round(((au.total_pages - au.used_pages) * (8/1024.00)), 2) AS DECIMAL(36,2))FROM sys.schemas sJOIN sys.tables  t ON s.schema_id = t.schema_idJOIN sys.indexes i ON t.object_id = i.object_idJOIN (SELECT [object_id], index_id, partition_count=count(*), [rows]=sum([rows]), data_compression_cnt=count(distinct [data_compression])FROM sys.partitionsGROUP BY [object_id], [index_id]) p ON i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id]JOIN (SELECT p.[object_id], p.[index_id], total_pages = sum(a.total_pages), used_pages = sum(a.used_pages), data_pages=sum(a.data_pages)FROM sys.partitions pJOIN sys.allocation_units a ON p.[partition_id] = a.[container_id]GROUP BY p.[object_id], p.[index_id]) au ON i.[object_id] = au.[object_id] AND i.[index_id] = au.[index_id]WHERE t.is_ms_shipped = 0 -- Not a system table
小开 
CREATE TABLE #tmp_table_info(id int identity(1,1),tblname varchar(200));CREATE TABLE #SpaceUsed(TableName sysname,NumRows BIGINT,ReservedSpace VARCHAR(50),DataSpace VARCHAR(50),IndexSize VARCHAR(50),UnusedSpace VARCHAR(50))
insert into #tmp_table_infoselect s.name+'.'+t.namefrom sys.tables tinner join sys.schemas s on t.schema_id = s.schema_idwhere t.type = 'U';
declare @min int =1,@max int = 0select @max = count(*)from #tmp_table_infowhile(@min<=@max)begindeclare @tablename varchar(200)select @tablename=tblnamefrom #tmp_table_infowhere id =@min
DECLARE @str VARCHAR(500)SET @str =  'sp_spaceused '''+@tablename+''''INSERT INTO #SpaceUsedEXEC (@str)set @min =@min + 1end;select @@SERVERNAME as servername,DB_NAME() as DatabaseName,CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024 as ReservedSpace_MB,CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024 as DataSpace_MB,CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024 as IndexSpace_MB,CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024 as UnusedSpace_MB from #SpaceUseddrop table #tmp_table_infodrop table #SpaceUsed
小开

也许表格有一个更多的分区文件&必须显示文件顺序

SELECTT1.Name                                       AS TableName,T5.Name                                       AS SchemaName,T3.partition_number                           AS PartionNumber,T3.Rows                                       AS RowsCount,SUM(T4.total_pages) * 8                       AS TotalSpaceKB,SUM(T4.used_pages) * 8                        AS UsedSpaceKB,(SUM(T4.total_pages) - SUM(T4.used_pages)) * 8 AS UnusedSpaceKBFROMsys.objects T1  INNER JOINsys.indexes T2 ON T1.object_id = T2.object_id  INNER JOINsys.partitions T3 ON T2.object_id = T3.object_id AND T2.index_id = T3.index_id  INNER JOINsys.allocation_units T4 ON T3.partition_id = T4.container_id LEFT JOINsys.schemas T5 ON T1.schema_id = T5.schema_idWHERET1.type='U'GROUP BYT1.Name, T5.Name, T3.Rows,T3.partition_numberORDER BYT1.Name,T3.partition_number;
小开

我要感谢格雷格·洛先生提出这个问题：

SELECT o.name AS ObjectName,SUM(reserved_page_count) * 8.0 / 1024 AS SizeinMBFROM sys.dm_db_partition_stats AS psINNER JOIN sys.sysobjects AS oON ps.object_id = o.idGROUP BY o.nameORDER BY SizeinMB DESC;

提交答案