The Multifunctioning DBA

Nov 29 2011   5:35PM GMT

Powershell Gather Table Data

Colin Smith Colin Smith Profile: Colin Smith

I had a person on my team that wanted to identify tables in our enterprise that might be good candidates for compression. I thought it might help him out if I had a script that would look at every table in our enterprise that exists in a SQL Server database and give him some basic info that may help out. Like Row Count and Data Space Usage. So here is the powershell script that I came up with.

$i = 0
$output = @()
$sqlinstances = Invoke-Sqlcmd -ServerInstance servername -Database dba -Query “select instance  from instance where isactive = 1”

foreach($sqlinstance in $sqlinstances)

$iname = “$sqlinstance.instance”
$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $iname
$dbs = $s.Databases | where{$_.Name -ne “Master” -and $ -ne “Model” -and  $ -ne “tempdb” -and $ -ne “msdb”}
foreach($db in $dbs)
{$i = $i +1
$t = 0
$dbname = $db.Name
$tables = $db.Tables
$numtable = $tables.count
foreach($table in $tables)
$t = $t + 1
echo “table $t of $numtable”

$tname = $
$tschema = $table.schema
$tpart = $table.ispartitioned
$tcomp = $table.hascompressedpartitions
$trows = $table.rowcount
$tdata = $table.dataspaceused
$Object = New-Object PSObject
$Object | add-member Noteproperty Instance $name
$Object | add-member Noteproperty Database $dbname
$Object | add-member Noteproperty Table $tname
$Object | add-member Noteproperty Schema $tschema
$Object | add-member Noteproperty Partitioned $tpart
$object | add-member noteproperty Compressed $tcomp
$Object | add-member Noteproperty RowCount $trows
$Object | add-member Noteproperty TableSize $tdata

$output = $output + $object

$output | Sort-Object -Property RowCount -Descending | select Instance, database, table, rowcount | Out-GridView

Hope this helps.

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: