WIDBA

WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Thursday, October 4, 2012

Database Table Sizes in Powershell


Just wanted to share a very simple script that I have used far more than I ever thought I would.  The initial purpose was to get the average cost of a row of data, but this has been nice to find tables that are filling up quick, have a high row to space ratio, etc.



[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") "MySQLServerName"

$sqlServer.Databases | Where-Object{$_.Name -eq "BeerDB"} | 
    Select-Object Tables -ExpandProperty Tables | Sort-Object -Property RowCount -Descending  |
    Select Name, DataSpaceUsed , IndexSpaceUsed, RowCount,
         @{Label="TableSizeInMB";Expression={($_.DataSpaceUsed + $_.IndexSpaceUsed) / 1024}},
         @{Label="AvgSpacePerRowKB";Expression={($_.DataSpaceUsed + $_.IndexSpaceUsed) / $_.RowCount}}  | out-gridview -Title "SpaceUsed"

1 comment: