Get size of all tables in database

I have inherited a fairly large SQL Server database. 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?

---

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    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 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 
    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

---

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 .

Right click on the database

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. See http://msdn.microsoft.com/en-gb/library/bb510680.aspx

---

sp_spaceused can get you information on the disk space used by a table, indexed view, or the whole database.

For example:

USE MyDatabase; GO

EXEC sp_spaceused N'User.ContactInfo'; GO

This reports the disk usage information for the ContactInfo table.

To use this for all tables at once:

USE MyDatabase; GO

sp_msforeachtable 'EXEC sp_spaceused [?]' GO

You can also get disk usage from within the right-click Standard Reports functionality of SQL Server. To get to this report, navigate from the server object in Object Explorer, move down to the Databases object, and then right-click any database. From the menu that appears, select Reports, then Standard Reports, and then "Disk Usage by Partition: [DatabaseName]".

链接地址: http://www.djcxy.com/p/25980.html

上一篇: SQL Server Management Studio 2012在哪里？

下一篇: 获取数据库中所有表的大小