This 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 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:

123

CREATE DATABASE [123];

"DoubleQuotes"

CREATE DATABASE ["DoubleQuotes"];

[Brackets]

CREATE DATABASE [[Brackets]]];

[BracketOnLeft

CREATE DATABASE [[BracketOnLeft];

BracketOnRight]

CREATE DATABASE [BracketOnRight]]];

master (Restoring...)

CREATE DATABASE [master (Restoring...)];

Let'sApostrophe

CREATE DATABASE [Let'sApostrophe];

Contains Space

CREATE DATABASE [Contains Space];

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;

Contains/ForwardSlash

CREATE DATABASE [Contains/ForwardSlash];

Contains\BackSlash

CREATE DATABASE [Contains\BackSlash];

EndsWithPeriod.

CREATE DATABASE [EndsWithPeriod.];

.

CREATE DATABASE [.];

💩

CREATE DATABASE [💩];

ぷ

CREATE DATABASE [ぷ];

CaseSensitive

CREATE DATABASE CaseSensitive COLLATE Slovenian_100_CS_AS_KS_WS;

BinarySensitive

CREATE DATABASE BinarySensitive COLLATE Slovenian_100_BIN2;

DBOffline

CREATE DATABASE DBOffline;    ALTER DATABASE DBOffline    SET OFFLINE;

DBReadOnly

CREATE DATABASE DBReadOnly;   ALTER DATABASE DBReadOnly   SET READ_ONLY;

DBSingleUser

CREATE DATABASE DBSingleUser; ALTER DATABASE DBSingleUser SET SINGLE_USER;

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_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.