Identify uncompressed tables in SQL Server database with Powershell

Today I will illustrate how to check whether data compression is enabled on your SQL Server. This script is highly flexible and you may adjust it to your liking. I will only concentrate on compression today, in the next installment I will show how to modify this script and get other things done on your database server.

#This is how you comment in Powershell v.2 for big paragraphs

<#

Artem Ervits

Database Services Group

Check whether compression is enabled

Ver. 1.0.1 01/04/10 Added prompt to enter server name

Ver. 1.0.0 12/07/10 Initial Script

#>

#cls is an alias for clear screen

cls

#you may take input from a list of servers in your txt file but we’re going to concentrate on one server at a time right now.

#$hostname = Get-Content "C:\servers.txt"

echo

"Please Enter Server Name:"

#This next line will prompt you to enter a SQL Server Name.

$hostname

= Read-Host

#we’re making a SQL connection in this step

[

System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | Out-Null

$sqlserver

= New-Object (’Microsoft.SqlServer.Management.Smo.Server’) $hostname

#we’re looping through all databases on your server.

foreach

($db in $sqlserver.Databases)

{

#System databases and any SQL Server Reporting Services DBs will be skipped in this condition.

if($db.ID -gt 4 -and $db.Name -ne "ReportServer" -and $db.Name -ne "ReportServerTempDB")

{

#we’re looping through all of the tables in the database because data compression is a table property

foreach($table in $db.Tables)

{

#if compression is enabled, we will skip the table.

if($table.HasCompressedPartitions -ne $true)

{

#here we’re outputting the name of the database and the table that are not compressed. This is useful for when ‘someone’ has created a table on your db and you need to make sure that all tables are compressed.

Write-Host "----------------------------"

Write-Host "Database Name: " $db.Name Write-Host "Table Name: " $table.name Write-Host "Compression On: " $table.hascompressedpartitions

}

}

}

}

Write-Host

"----------------------------"

#that’s all folks!