SQL Server – List Table Sizes for All Databases

sql server

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.

Best Answer

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

Related Question