7

Is there a simple way of listing the size of every table in every database on a MSSQL server?

I have used a query on sys.tables to get results for a single database, but we have >100 databases per server, so a way of getting the same results but for all databases would be great.

Currently I'm having to create a temporary list of databases from master.sys.databases, and then iterate over that with a cursor, building a query and inserting the results into a temp table with EXEC sp_executeSQL @SQLString.

  • Object space information is stored in the database where the object resides. So there is no other way then to iterate using a list of databases. Which version of SQL Server are you using? – Edward Dortland Dec 20 '13 at 13:10
  • @cylindric, a useful link here – Biju jose Dec 20 '13 at 13:13
6

If you wanted to get this across all of your environment, for all of your databases...and you don't mind using PowerShell... You will need run this from a machine that at least has SQL Server 2008 Management Studio installed.


# 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}}
    }
}

As labeled the DataSpaceUsed SMO object outputs in "KB", you can modify this to be the measurement of your choice by just putting the abbreviated reference for it. So if I wanted "MB": $_.DataSpaceUsed/1MB.

In the function ([string[]]$server), the brackets "[]" mean the parameter accepts an array of objects. So if you have your servers listed in a file you can call the function like so:


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

I prefer using Out-GridView initially to review the output, and it copies easily straight into Excel for me. You can also output this to the other supported formats of PowerShell if desired.

Example with screenshot, you can also just list the servers out: enter image description here

5

Taken from Stack-Overflow: Get size of all tables in database

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
  • 1
    That's exactly what I already have that doesn't do what I need - that just shows one database. – Cylindric Dec 20 '13 at 15:54
2

I used a merge of previous answers:

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' ;
1

You can try using sp_msforeachdb however a couple of warnings with this.

That being said I've used it successfully for several years now.

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

Basically it does a cursor and a replace on the ? with the DB name.

You could also try Aaron Bertrand's replacement version. I haven't tried it out myself but it is supposed to be better.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.