· Uwe Janke
Commands / Get-sqmDatabaseCollationReport

Get-sqmDatabaseCollationReport

SQL ConfigurationsqmSQLTool v1.9.116+ · Get🔓 VIEW ANY DEFINITION
Compares each database's default collation to the SQL Server instance collation. tempdb always uses the instance collation and cannot be changed independently, so a database whose collation differs from the instance is flagged as a warning: comparisons and joins between that database's char/varchar/nvarchar columns and tempdb objects (temp tables, table variables), or objects in another database with a different collation, can fail with Cannot resolve collation conflict unless every query adds an explicit COLLATE clause. Results are written as TXT and HTML report into -OutputPath, and the flat array of per-database result objects is still returned for further filtering.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMETarget SQL Server instance.
-SqlCredentialPSCredentialOptional, SQL or Windows credential. Without it: Windows authentication.
-Databasestring[]Optionalall user databasesRestrict the check to these database names.
-IncludeSystemswitchSwitch$falseInclude system databases in the check.
-OutputPathstringOptionalGet-sqmDefaultOutputPath\CollationReportsOutput directory for the TXT/HTML report files.
-NoOpenswitchSwitch$falseDo not automatically open the generated report.
-NoReportswitchSwitch$falseSkip writing the TXT/HTML files entirely - only the object array is returned.
-EnableExceptionswitchSwitch$falseThrow exceptions immediately instead of returning as errors.

Execution Flow

START dbatools installed? NO throw: dbatools not found YES Read the instance collation SERVERPROPERTY('Collation') - the collation tempdb is forced to use Enumerate databases -Database filters the list · system databases only with -IncludeSystem Database collation = instance collation? NO Status = Warning Joins against tempdb objects can fail with "Cannot resolve collation conflict" YES Status = OK No COLLATE clause needed for tempdb or cross-database comparisons -NoReport? YES Return objects only No files written NO Write TXT + HTML report -OutputPath, default <config>\CollationReports · opened unless -NoOpen Return one [PSCustomObject] per database SqlInstance · Database · DatabaseCollation · ServerCollation · Status DONE

Examples

Check all user databases on one instance
Get-sqmDatabaseCollationReport -SqlInstance "SQL01"
Include system databases and write to a custom folder
Get-sqmDatabaseCollationReport -SqlInstance "SQL01" -IncludeSystem -OutputPath "D:\Reports"
Only the mismatches, no report files
Get-sqmDatabaseCollationReport -SqlInstance "SQL01" -NoReport | Where-Object Status -eq 'Warning'
Sweep a list of instances
"SQL01","SQL02","SQL03" | ForEach-Object { Get-sqmDatabaseCollationReport -SqlInstance $_ -NoOpen }
← Back
Command Index
Related
Get-sqmSupportedCollations
Related
Invoke-sqmCollationChange