List table sizes for all tables on all databases

code Snippets
,
1

This is something I had a need for the other day. Trying to find which database out of about 30 had records that I could use to test a feature. Google, Database Administrators Stack Exchange and Powershell to the rescue!

My modified version of the answer at that link:
instead of a list of servers you send a list of connection strings

# Load SMO
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

function Get-TableSize ([string[]]$connectionStrings) {
    foreach ($connectionString in $connectionStrings) {
        $sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
        $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
        $s = new-object Microsoft.SqlServer.Management.Smo.Server($conn)
        $s.Databases.Tables | 
            ? {-Not $_.IsSystemObject} | 
                Select @{Label="Server";Expression={$conn.ServerInstance}},
                    @{Label="DatabaseName";Expression={$_.Parent}}, 
                    @{Label="TableName";Expression={$_.Name}}, 
                    @{Label="SizeKB";Expression={$_.DataSpaceUsed}}
    }
}

$list = @('Server=server\sql2008;User Id=myuser;Password=secret')
Get-TableSize -connectionStrings $list | Out-GridView

The result:

Sort/filter it in place or copy and paste to excel for further analysis as needed.