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 $_.name -ne “Model” -andĀ  $_.name -ne “tempdb” -and $_.name -ne “msdb”}
foreach($db in $dbs)
{$i = $i +1
$i
$t = 0
$dbname = $db.Name
$tables = $db.Tables
$numtable = $tables.count
foreach($table in $tables)
{
$t = $t + 1
echo “table $t of $numtable”

$tname = $table.name
$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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: