Compare column names for case sensitivity

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
, TableName1
, ColName2;

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.

-Jen

3 thoughts on “Compare column names for case sensitivity

  1. Pingback: Comparing Column Names – Curated SQL

  2. Joe Celko

    Capitalization is kind of fun. There is only for writing systems that actually use it – Latin, Cyrillic, Greek, and Arabic (their letterforms have changes for initial position, middle position, terminal position and standalone). Bad news is that ISO conventions vary quite a bit within their standards. Some are case-sensitive, some are not; however, they did agree that every Unicode language should support a basic Latin set and some simple punctuation marks; this is basically what the old ASCII used to be. The goal is to be sure that no matter what you were writing and you could always embed an ISO encoding, like metric system abbreviations, etc.

Comments are closed.