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


#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"


"Please Enter Server Name:"

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


= Read-Host

#we’re making a SQL connection in this step


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


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

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


($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: " $ Write-Host "Compression On: " $table.hascompressedpartitions







#that’s all folks!


Popular posts from this blog

Vista Vulnerability Report mentions Ubuntu 6.06 LTS

Running CockroachDB with Docker Compose and Minio, Part 2

Doing "print screen" on a Mac is a pain in the ass