Two Things That Break Silently at Migration or Decommission Time
Two checks that rarely matter until the exact moment they do: does this database’s collation match the server’s (and every other database it might join against), and does anything in this database reach into another database or another server by name. Both are invisible during normal operation and both cause real failures the moment a migration, rename, or decommission changes what they were quietly depending on.
Collation Conflicts flags any database whose collation differs from the server default. Cross-Database Dependencies finds every object that references another database or server by a 3-part or 4-part name, or through a synonym, the things a decommission review specifically needs to catch.
Why Collation Conflicts and Cross-Database Dependencies Matter
- A collation mismatch between two databases doesn’t fail until a query joins across them on a string column, at which point it fails with an explicit collation conflict error, often confusing to whoever hits it first
- Cross-database references are invisible from inside the referencing database alone, nothing about the object itself looks unusual unless you specifically check what it points at
- Both checks answer questions a migration or decommission plan needs answered up front, not discovered mid-cutover when a query starts failing
- Synonyms pointing at another database are especially easy to miss, the synonym itself looks local, the real target is hidden behind an extra layer of indirection
When to Run These Scripts
- Before any database migration, rename, or decommission, both checks are exactly what that kind of review needs
- Collation Conflicts: after restoring a database from a different source instance, to confirm it matches the target server’s expectations
- Cross-Database Dependencies: before removing or renaming any database, to confirm nothing elsewhere depends on it by name
- Routine health checks on any multi-database environment, since both issues accumulate quietly over time
The Scripts
Get-CollationConflicts — Does This Database Match the Server?
/*
Script Name : Get-CollationConflicts
Category : monitoring
Purpose : Databases whose collation differs from the server collation — a common source of implicit conversion errors and failed JOIN operations.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-collation-conflicts-and-cross-database-dependencies/)
Requires : VIEW ANY DATABASE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-collation-conflicts-and-cross-database-dependencies/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
DECLARE @server_collation NVARCHAR(128) = CAST(SERVERPROPERTY('Collation') AS NVARCHAR(128));
SELECT
@server_collation AS server_collation,
d.name AS database_name,
d.collation_name AS database_collation,
CASE WHEN d.collation_name <> @server_collation
THEN 'MISMATCH' ELSE 'OK' END AS collation_status,
d.state_desc,
d.recovery_model_desc,
d.database_id
FROM sys.databases d
WHERE d.state_desc = 'ONLINE'
ORDER BY
CASE WHEN d.collation_name <> @server_collation THEN 0 ELSE 1 END,
d.name;Get-CrossDatabaseDependencies — What Reaches Outside This Database
/*
Script Name : Get-CrossDatabaseDependencies
Category : monitoring
Purpose : Objects in the current database that reference other databases via 3-part names or linked servers — critical to find before a migration, rename, or decommission.
Note: only captures statically-resolvable references. Dynamic SQL built at runtime will not appear here.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-collation-conflicts-and-cross-database-dependencies/)
Requires : VIEW DEFINITION
*/
-- Blog: https://sqldba.blog/dba-scripts-get-collation-conflicts-and-cross-database-dependencies/
-- SAFE:ReadOnly
-- IMPACT:Low
-- SCOPE:CurrentDatabase
SET NOCOUNT ON;
/* ── Static cross-database references from sys.sql_expression_dependencies ─ */
SELECT
'Object Reference' AS reference_type,
DB_NAME() AS source_database,
OBJECT_SCHEMA_NAME(d.referencing_id) AS source_schema,
OBJECT_NAME(d.referencing_id) AS source_object,
o.type_desc AS source_object_type,
COALESCE(d.referenced_server_name + '.', '')
+ COALESCE(d.referenced_database_name + '.', '')
+ COALESCE(d.referenced_schema_name + '.', '')
+ COALESCE(d.referenced_entity_name, '?') AS referenced_target,
d.referenced_server_name,
d.referenced_database_name,
d.referenced_schema_name,
d.referenced_entity_name
FROM sys.sql_expression_dependencies d
JOIN sys.objects o ON o.object_id = d.referencing_id
WHERE ( d.referenced_database_name IS NOT NULL
AND d.referenced_database_name <> DB_NAME()
)
OR d.referenced_server_name IS NOT NULL
UNION ALL
/* ── Synonyms pointing to other databases or servers ────────────────────── */
SELECT
'Synonym' AS reference_type,
DB_NAME() AS source_database,
SCHEMA_NAME(syn.schema_id) AS source_schema,
syn.name AS source_object,
'SYNONYM' AS source_object_type,
syn.base_object_name AS referenced_target,
/* Parse server from 4-part name (server.db.schema.obj) */
CASE WHEN syn.base_object_name LIKE '[[]%].%.[%].%'
THEN PARSENAME(REPLACE(syn.base_object_name, '].[', '.'), 4)
ELSE NULL END AS referenced_server_name,
/* Parse database: 4-part = part 3, 3-part = part 3 */
CASE WHEN LEN(syn.base_object_name) - LEN(REPLACE(syn.base_object_name, '.', '')) >= 2
THEN PARSENAME(REPLACE(REPLACE(syn.base_object_name,'[',''),']',''), 3)
ELSE NULL END AS referenced_database_name,
NULL AS referenced_schema_name,
NULL AS referenced_entity_name
FROM sys.synonyms syn
WHERE syn.base_object_name LIKE '%.%.%' /* at least a 3-part name */
ORDER BY reference_type, source_object, referenced_target;This only catches statically-resolvable references, anything built inside dynamic SQL at runtime won’t show up here, worth keeping in mind as a real limit rather than assuming a clean result means zero cross-database dependencies.
How To Run From The Repo
Clone DBA Tools, initialize and run either script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Every database's collation vs the server default:
.\run.ps1 Get-CollationConflicts
# What the current database references outside itself:
.\run.ps1 Get-CrossDatabaseDependencies -Database YourDatabase
# To run against a remote sql server:
.\run.ps1 Get-CollationConflicts -ServerInstance SQLSERVER01
These scripts live in the repo at:
sql/monitoring/features/Get-CollationConflicts.sqlsql/monitoring/features/Get-CrossDatabaseDependencies.sql
Example Output
Real output from this lab instance, not staged. Get-CollationConflicts across all 24 online databases: every single one shows collation_status = OK, matching the server’s SQL_Latin1_General_CP1_CI_AS, a genuinely clean result.
Get-CrossDatabaseDependencies, run against WatchtowerMetrics:
A real, genuine finding, one view in WatchtowerMetrics reaches into GrowthLab by a 3-part name. If GrowthLab were ever decommissioned or renamed without checking this first, that view would break the moment someone queried it.
Understanding the Results
Collation Conflicts:
- collation_status = MISMATCH — this database will hit collation conflict errors on any query joining a string column against a database or the server using a different collation
- All databases OK — genuinely clean, no collation-related surprises waiting; still worth re-checking after restoring a database from an unfamiliar source
Cross-Database Dependencies:
- reference_type = “Object Reference” — a real, statically-detectable 3-part or 4-part name reference; this is exactly what a decommission review is looking for
- reference_type = “Synonym” — the synonym itself looks local from inside the database, but its
base_object_namepoints elsewhere; check every synonym before assuming an object is self-contained - Zero rows — no statically-resolvable cross-database references found; remember this doesn’t rule out references built inside dynamic SQL at runtime
Best Practices
- Run Collation Conflicts after restoring any database from a different source instance, don’t assume collation carried over cleanly
- Run Cross-Database Dependencies before renaming, moving, or decommissioning any database, in both directions, check what the target references and what references the target
- Remember the dynamic-SQL blind spot on Cross-Database Dependencies, grep application code for cross-database SQL built at runtime as a supplementary check
- Fix a collation mismatch deliberately (via
COLLATEin the query, or a database-level collation change) rather than letting implicit conversions silently degrade query performance
Related Scripts
You may also find these scripts useful:
- SQL Server Collation: Case Sensitivity and the Cross-Database Trap, the fundamentals behind these findings
- Migration Risk Assessment
- Linked Servers
- Migration Login Audit and Post-Migration Validation
- DBA Scripts: The Complete Guide, the map across every script on this site
Frequently Asked Questions
Why does a collation mismatch cause implicit conversion issues specifically?
Comparing or joining string columns from two different collations requires SQL Server to pick one collation to evaluate the comparison under, which it either does implicitly (with a performance cost and sometimes unexpected comparison behavior) or refuses to do at all, raising an explicit collation conflict error. Neither outcome is desirable, matching collations avoids the situation entirely.
Does a clean Cross-Database Dependencies result guarantee no cross-database references exist?
No, it guarantees no statically-resolvable references exist in T-SQL object definitions and synonyms. Dynamic SQL that builds a cross-database or cross-server reference at runtime (string-concatenated EXEC, for example) won’t be caught by this script, since it doesn’t exist as a static reference until the moment it executes.
Summary
Both of these checks are quiet until a migration, rename, or decommission changes the thing they were depending on, at which point they fail loudly. Collation Conflicts confirms every database agrees with the server on how to compare strings. Cross-Database Dependencies finds what would actually break if a specific database disappeared or moved.
Run both before any migration or decommission, and treat a nonzero cross-database reference as a real dependency to resolve, not a detail to note and move past.
Leave a Reply