Me as a full time employee: “Oh wow, it’s afternoon already!!”
Me as an entrepreneur: “Oh hell, it’s afternoon already.”
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.
Minion CheckDB is available for download as of February 1, 2017!
In celebration, we’re having a Minion CheckDB webinar on two different days:
And, we will be giving away 3 licenses of Minion Enterprise to one lucky winner* at each webinar. Must be present to win!
Save the Feb 1 event to your calendar: Outlook (ics)
Save the Feb 3 event to your calendar: Outlook (ics)
Minion CheckDB completes the MinionWare maintenance and backups suite in style. Each solution is plug-and-play for the busy DBA, and deeply configurable for those shops with in-depth needs.
This new module is MinionWare’s most ambitious free release yet, featuring all of the rich scheduling and logging functionality in previous products, plus remote CheckDB, multithreading, custom snapshots, rotational scheduling, and more.
In this webinar we’ll show you how this FREE tool by MinionWare can meet your needs with almost effortless management. You are going to LOVE it.
*(Giveaway offer is not open to previous ME winners.)