Audit SQL Server collation with Powershell

Here’s just a quick Powershell script I knocked up to find out the server-level and database collations on multiple servers. Just specify each SQL Server in the array called $servers and you’re good to go.

# Load SMO
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
# Specify servers here
$servers = @("localhost\sqlexpress", "localhost");

foreach($server in $servers)
{
	# Create a SMO server object
	$srv = New-Object Microsoft.SqlServer.Management.SMO.Server $server;
	# Server collation
	Write-Host $server "- Server Collation: " $srv.Collation;
	Write-Host "==========================================================";
	$databases = $srv.Databases;
	# Check the collation of each database
	foreach($db in $databases)
	{
		Write-Host $db.Name "-" $db.Collation;
	}
	Write-Host "";
}

The script will produce output similar to below;

localhost\sqlexpress - Server Collation: Latin1_General_CI_AS
==========================================================
AdventureWorks - Latin1_General_CI_AS
AdventureWorksDW - Latin1_General_CI_AS
AdventureWorksDW2008 - Latin1_General_CI_AS
AdventureWorksLT - Latin1_General_CI_AS
AdventureWorksLT2008 - Latin1_General_CI_AS

localhost - Server Collation: Latin1_General_CI_AS
==========================================================
ft_test - Latin1_General_CI_AS
master - Latin1_General_CI_AS
model - Latin1_General_CI_AS