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.
Database snapshots have been available in SQL Server Enterprise edition in the last several versions. But if you’re lucky enough to have 2016 at work, go on and upgrade to SP1….you get extra-lucky special database snapshots in any version!
(Be sure to read the footnote for “Database snapshots” in that link.)
What are database snapshots for?
Oh what’s that? You don’t use snapshots? Well let’s see a couple of good use cases for them:
- Take a database snapshot before an upgrade, data load, or major change. It’s WAY faster than a backup, and you can roll back the changes if you need to, almost instantly.
- Customize integrity checks. When you run DBCC CheckDB or DBCC CheckTable, behind the scenes SQL Server creates a snapshot of the database to run the operation against. You can also choose to create a custom snapshot…among other reasons, so you can specify where the snapshot files are placed. This is especially useful for shops running tight on disk space.
Speaking of integrity checks and snapshots, Minion CheckDB is slated to come out on this coming February 1. Be on the lookout…it’s going to blow your mind.
Quick test. Why won’t this return the string “Hi”?
CREATE PROC dbo.Testing @Val VARCHAR
IF @Val = 'One'
SELECT 'Hi' AS msg;
EXEC dbo.Testing @Val = 'One';
Try it yourself, and answer in the comments. I’ll update this (in comments) in a week or so with the answer, if no one’s gotten it.
And yes, I did this exact thing. You’re never too old to do something like this.