Use PowerShell to determine MS SQL table used data space

Yet another issue to be solved: we have SQL Expression installed at our branch server and it allows only 4Gb per table. So - what's the solution? Of course, PowerShell!

Right click on database - "Start PowerShell". (Note: I do NOT know how to enter MS SQL mode from normal PowerShell). And now we can have some magic with our database! The following script shows databases that are too heavy and can be cleaned up after some consideration:

ls tables | sort -Property DataSpaceUsed -Descending | ? {$_.DataSpaceUsed -gt 1024} | % {$_.Name + " takes " + [math]::Round($_.DataSpaceUsed / 1024 ) + " Mb"}

Wasn't that just sexy? :) Here goes the step-by-step explanation:

ls tables

Listing all db tables.

sort -Property DataSpaceUsed -Descending

Sorting descending by DataSpaceUsed field - we need to see only the heaviest.

? {$_.DataSpaceUsed -gt 1024}

Taking only tables that take more than 1Mb in data space.

% {$_.Name + " takes " + [math]::Round($_.DataSpaceUsed / 1024 ) + " Mb"}

Making output look nice and actually showing table name.