List table sizes for all tables on all databases
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: