T-SQL Tuesday #143 : Preparing for the Worst… Metadata

T-SQL Tuesday #143 : Short code examplesThis month, John McCormack (twitter) asks us: “What are your go-to short, handy scripts”? I'm going to go a little meta on John here and give a little peek into what I do to bullet-proof the short, handy scripts I write for other folks (e.g. answering questions on Stack Overflow or Database Administrators Stack Exchange).

Photo by John MortonPhoto by John Morton on flickr

Over the past several months, I've really tried to do more than just answer questions; more often than not, I post verifiable and working examples of my solutions on db<>fiddle (the brain child of Jack Douglas). Doing this is good for the reader, because it allows them to test edge cases missing from their initial question, and it might also teach them how to set up tables and sample data (see How do I ask a good database question?).

More importantly, it makes me double-check my own damned work, both on db<>fiddle and on my own machine.

db<>fiddle is great at testing scripts inside a specific database, but since I often write scripts that do all kinds of "for each" operations (and save those scripts and reuse them on future questions), I always create the following databases on my own systems or VMs:

Database Name Actual Name Code
Starts with a number
123
CREATE DATABASE [123];
Surrounded in double quotes
"DoubleQuotes"
CREATE DATABASE ["DoubleQuotes"];
Surrounded in square brackets
[Brackets]
CREATE DATABASE [[Brackets]]];
Starts with an opening square bracket
[BracketOnLeft
CREATE DATABASE [[BracketOnLeft];
Ends with a closing square bracket
BracketOnRight]
CREATE DATABASE [BracketOnRight]]];
Trick name: master (Restoring…)
master (Restoring...)
CREATE DATABASE [master (Restoring...)];
Contains a single quote
Let'sApostrophe
CREATE DATABASE [Let'sApostrophe];
Contains a space
Contains Space
CREATE DATABASE [Contains Space];
Contains a tab
Contains	Tab
DECLARE
  @dp nvarchar(2048) = CONVERT(nvarchar(2048), 
                       SERVERPROPERTY(N'InstanceDefaultDataPath')), 
  @lp nvarchar(2048) = CONVERT(nvarchar(2048), 
                       SERVERPROPERTY(N'InstanceDefaultLogPath'));;
 
DECLARE @sql nvarchar(max) = N'CREATE DATABASE ' 
  + QUOTENAME(N'Contains' + char(9) + N'Tab') + N' 
  ON     (name = ContainsTabData, filename = N''' 
          + @dp + N'ContainsTabData.mdf'')
  LOG ON (name = ContainsTabLog,  filename = N''' 
          + @lp + N'ContainsTabLog.ldf'');';
 
EXEC master.sys.sp_executesql @sql;
The only database that requires extra handling is the one that contains a tab, because SQL Server doesn't know how to generate file names when that character is present. I am sure there are a bunch of other less common but equally exotic characters that may cause the same problem, or you could just always hard-code the file names and paths instead of being lazy like me and letting SQL Server define them.
Contains a forward slash
Contains/ForwardSlash
CREATE DATABASE [Contains/ForwardSlash];
Contains a backward slash
Contains\BackSlash
CREATE DATABASE [Contains\BackSlash];
Ends with a period
EndsWithPeriod.
CREATE DATABASE [EndsWithPeriod.];
Is just a period
.
CREATE DATABASE [.];
Poop emoji
💩
CREATE DATABASE [💩];
Japanese Running Man ("PU")
CREATE DATABASE [];
Other Database Criteria:
Case sensitive
CaseSensitive
CREATE DATABASE CaseSensitive COLLATE Slovenian_100_CS_AS_KS_WS;
Binary sensitive
BinarySensitive
CREATE DATABASE BinarySensitive COLLATE Slovenian_100_BIN2;
One database offline
DBOffline
CREATE DATABASE DBOffline;    ALTER DATABASE DBOffline    SET OFFLINE;
One database read-only
DBReadOnly
CREATE DATABASE DBReadOnly;   ALTER DATABASE DBReadOnly   SET READ_ONLY;
One database in single-user mode
DBSingleUser
CREATE DATABASE DBSingleUser; ALTER DATABASE DBSingleUser SET SINGLE_USER;
One database that is actually restoring
DBRestoring
CREATE DATABASE DBRestoring;
 
BACKUP DATABASE DBRestoring 
  TO DISK   = N'C:\temp\dbrestoring.bak' 
  WITH INIT, COMPRESSION;
 
RESTORE DATABASE DBRestoring 
  FROM DISK = N'C:\temp\dbrestoring.bak' 
  WITH REPLACE, NORECOVERY;

This is how I actually tested sp_ineachdb, to make sure it was ready for just about any bad idea anyone used to name a database, and could handle various possible database states (for a lot more background on this procedure, and why it is better than the undocumented, unsupported, and buggy sp_msforeachdb, see this and this). Here you can see that the procedure works against all these poorly-named databases, and skips databases that are inaccessible (rather than raise an exception).

Testing sp_ineachdbTesting sp_ineachdb

Notice that Object Explorer handles most names like a champ but it, too, doesn't quite know what to do with that tab character. If you want to build a much more comprehensive battery of test databases or other entities with all kinds of funky and non-printing characters, see Rob Volk's hilarious session, Revenge: The SQL!

This way, when I write dynamic SQL that generates commands based on someone else's naming conventions, I'm prepared for the worst. And of course I always surround database names with QUOTENAME() so that when the script is executed it doesn't hit any issues. Never manually place [ and ] around entity names, because that won't properly escape all problematic characters.

There are some other things I do that follow a similar theme, though I won't reproduce all the code here:

  • Both a case-sensitive instance and a binary-sensitive instance
  • Databases in earlier compat levels
  • A database with forced parameterization
  • A database containing:
    • 1,000 tables
    • Table names matching each of the name criteria above
    • Tables with column, constraint, and index names matching each of the name criteria above
    • A table with 1,000 columns
    • A disabled index
    • Three schemas; no objects in dbo, all objects in schema2, user has default schema schema3
    • Users with names matching each of the name criteria above
  • Other system criteria:
    • Jobs with names matching each of the name criteria above
    • Logins with names matching each of the name criteria above
    • Logins that were denied access to a specific database

This list is not exhaustive, but it comprises most of the edge cases I remember coming across while helping folks solve "do this to all <object or entity>"-type problems.

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, speak frequently at conferences, and write at SQLPerformance and MSSQLTips. In real life I am a father of two, an architect at Wayfair, and my pronouns are he/him.

3 Responses

  1. Great list Aaron! I frequently do some of what you've got above but I've never bothered formalizing it to this degree. It's a fantastic idea and I'll have to "borrow" it from you. 🙂

  1. October 12th, 2021

    […] Aaron Bertrand takes us to the Island of Misfit Databases: […]

  2. October 12th, 2021

    […] may perform but doesn’t quite rise to the level of automation such as what Aaron Bertrand shared here). Please go and check the invite from John – […]