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

本文翻译自：Get size of all tables in database

I have inherited a fairly large SQL Server database. 我继承了一个相当大的SQL Server数据库。 It seems to take up more space than I would expect, given the data it contains. 考虑到它包含的数据，它似乎占用了比我预期更多的空间。

Is there an easy way to determine how much space on disk each table is consuming? 有没有一种简单的方法可以确定每个表占用的磁盘空间大小？

---

#1楼

参考：https://stackoom.com/question/X79i/获取数据库中所有表的大小

---

#2楼

My post is only relevant for SQL Server 2000 and has been tested to work in my environment. 我的帖子只与SQL Server 2000相关，并且已经过测试，可以在我的环境中使用。

This code accesses All possible databases of a single instance , not just a single database. 此代码访问单个实例的所有可能数据库 ，而不仅仅是单个数据库。

I use two temp tables to help collect the appropriate data and then dump the results into one 'Live' table. 我使用两个临时表来帮助收集适当的数据，然后将结果转储到一个“实时”表中。

Returned data is: DatabaseName, DatabaseTableName, Rows (in the Table), data (size of the table in KB it would seem), entry data (I find this useful for knowing when I last ran the script). 返回的数据是：DatabaseName，DatabaseTableName，Rows（在表中），数据（以KB表示的表的大小），条目数据（我发现这对于了解我上次运行脚本的时间非常有用）。

Downfall to this code is the 'data' field is not stored as an int (The chars 'KB' are kept in that field), and that would be useful (but not totally necessary) for sorting. 这个代码的缺陷是“数据”字段没有存储为int（字符'KB'保存在该字段中），这对于排序是有用的（但不是完全必要的）。

Hopefully this code helps someone out there and saves them some time! 希望这段代码可以帮助那些人并节省一些时间！

CREATE PROCEDURE [dbo].[usp_getAllDBTableSizes]

AS
BEGIN
   SET 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 FOR   
   SELECT TableName FROM #DatabaseTables

   DECLARE AllDatabaseNames CURSOR LOCAL READ_ONLY FOR   
   SELECT DBName FROM #DatabaseTables

   DECLARE @DBName sysname  
   OPEN AllDatabaseNames  

   DECLARE @TName sysname
   OPEN AllDatabaseTables  

   WHILE 1=1 BEGIN 
      FETCH NEXT FROM AllDatabaseNames INTO @DBName  
      FETCH NEXT FROM AllDatabaseTables INTO @TName 
      IF @@FETCH_STATUS<>0 BREAK  
      INSERT INTO #AllDatabaseTableSizes
         EXEC ( 'EXEC ' + @DBName + '.dbo.sp_spaceused ' + @TName) 

   END 

   --http://msdn.microsoft.com/en-us/library/aa175920(v=sql.80).aspx
   INSERT INTO rsp_DatabaseTableSizes (DatabaseName, name, [rows], data)
      SELECT   [dbname], name, [rows],  data FROM #DatabaseTables
      INNER JOIN #AllDatabaseTableSizes
      ON #DatabaseTables.TableName = #AllDatabaseTableSizes.Name
      GROUP BY [dbname] , name, [rows],  data
      ORDER 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 name 
      FROM [rsp_DatabaseTableSizes]
      GROUP BY name
      HAVING COUNT(*) > 1
      )

   DROP TABLE #DatabaseTables
   DROP TABLE #AllDatabaseTableSizes

   CLOSE AllDatabaseTables  
   DEALLOCATE AllDatabaseTables  

   CLOSE AllDatabaseNames  
   DEALLOCATE AllDatabaseNames      
END

--EXEC [dbo].[usp_getAllDBTableSizes] 

In case you need to know, the rsp_DatabaseTableSizes table was created through: 如果您需要知道， rsp_DatabaseTableSizes表是通过以下方式创建的：

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

GO

---

#3楼

If you are using SQL Server Management Studio (SSMS), instead of running a query ( which in my case returned duplicate rows ) you can run a standard report . 如果您使用的是SQL Server Management Studio （SSMS），则可以运行标准报告 ，而不是运行查询（ 在我的情况下返回重复的行 ）。

1. Right click on the database 右键单击数据库
2. Navigate to Reports > Standard Reports > Disk Usage By Table 导航到报告>标准报告>按表格的磁盘使用情况

Note: The database compatibility level must be set to 90 or above for this to work correctly. 注意：必须将数据库兼容级别设置为90或更高才能使其正常工作。 See http://msdn.microsoft.com/en-gb/library/bb510680.aspx 请参阅http://msdn.microsoft.com/en-gb/library/bb510680.aspx

---

#4楼

Above queries are good for finding the amount of space used by the table (indexes included), but if you want to compare how much space is used by indexes on the table use this query: 上面的查询很适合查找表使用的空间量（包括索引），但是如果要比较表上索引使用的空间量，请使用以下查询：

SELECT
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM
    sys.indexes AS i JOIN 
    sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id JOIN 
    sys.allocation_units AS a ON a.container_id = p.partition_id
where [i].[is_primary_key] = 0 -- fix for size discrepancy
GROUP BY
    i.OBJECT_ID,
    i.index_id,
    i.name
ORDER BY
    OBJECT_NAME(i.OBJECT_ID),
    i.index_id

---

#5楼

I added a few more columns on top of marc_s answer: 我在marc_s回答的基础上添加了几个列：

with fs
as
(
select i.object_id,
        p.rows AS RowCounts,
        SUM(a.total_pages) * 8 AS TotalSpaceKb
from     sys.indexes i 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
WHERE 
    i.OBJECT_ID > 255 
GROUP BY 
    i.object_id,
    p.rows
)

SELECT 
    t.NAME AS TableName,
    fs.RowCounts,
    fs.TotalSpaceKb,
    t.create_date,
    t.modify_date,
    ( select COUNT(1)
        from sys.columns c 
        where c.object_id = t.object_id ) TotalColumns    
FROM 
    sys.tables t INNER JOIN      
    fs  ON t.OBJECT_ID = fs.object_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
ORDER BY 
    t.Name

---

#6楼

-- Show the size of all the tables in a database sort by data size descending
SET NOCOUNT ON
DECLARE @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