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

Leave a Reply