I’m reviewing the code for the upcoming Minion CheckDB, and one of the things we’re checking for is case consistency in column names. For example, if Table1 has a column named Col1, and Table2 has COL1, well that’s no good.
But, how do we easily find those mismatches on a system that’s not case sensitive? Easy: collations.
This query compares all columns with the same name (speaking case insensitively) for case equality:
SELECT OBJECT_NAME(c1.object_id) AS TableName1
, OBJECT_NAME(c.object_id) AS TableName2
, c1.name AS ColName1
, c.name AS ColName2
FROM sys.columns AS c1
INNER JOIN sys.columns AS c ON c1.object_id > c.object_id
WHERE UPPER(c1.name) = UPPER(c.name)
AND c1.name COLLATE Latin1_General_CS_AS <> c.name COLLATE Latin1_General_CS_AS
ORDER BY ColName1
Notice that we’re joining on c1’s object_id GREATER THAN c’s object_id. If we did <> (not equals), then we’d double our results (we’d see T1 C1 | T2 c1, and another row for T2 c1 | T1 C1).
We also have, in the where clause, UPPER(c1.name) = UPPER(c.name). We want column names that match, except for case.
And the “except for case” part comes with collating that last AND with a case sensitive collation: Latin1_General_CS_AS.
Easy. Done. Off you go.