Could not find database ‘x’, or why good object names are important.

SQL Server provides a method for specifying object names that contain special characters – you simply wrap the name in square brackets, as in [This-Is-A-Valid_$_ObjectName]. However, simply because you can do a thing, doesn’t mean you should! Names that contain a period should definitely be avoided since the period is a syntactic separator between parts of object names, as in database.schema.object. Adding periods into object names can result in “could not find database” error messages.

What’s my name, fool!!!

Today I saw a schema provided by a third-party vendor named DataDictionary.ArtifactManagement. I can see why including the period between DataDictionary and ArtifactManagement might seem like a nice way to visually separate the two details. However, please don’t do this. Unless every piece of code that dynamically touches that schema implements QUOTENAME(...) correctly, you’ll see errors like this:

Msg 2520, Level 16, State 5, Line 13
Could not find database ‘DataDictionary’. The database either does not exist, or was dropped before a statement tried to use it. Verify if the database exists by querying the sys.databases catalog view.

The code1 that produced the error above uses a cursor to dynamically call the deprecated DBCC SHOWCONTIG function to return index fragmentation statistics. The essential bit looks like:

Line 24 creates the DBCC SHOWCONTIG statement by appending the names of the schema, table, and index name. One might think a great way to fix this code would be to add QUOTENAME(...) around the object names, as in:

However, that doesn’t work, resulting in this error:

Msg 7999, Level 16, State 8, Line 1
Could not find any index named ‘[PK_DataDictionary_ArtifactManagement_AggregationArtifactsTable]’ for table ‘AggregationArtifactsTable’.

Luckily, removing the QUOTENAME(...) around the @index_name parameter allows the code to work without error, as in:

Having said that, I certainly don’t recommend using the deprecated DBCC SHOWCONTIG. Instead, use the sys.dm_db_index_physical_stats DMV to inspect index fragmentation, and use this script to reorganize or rebuild indexes.

1 – I did not write this code 🙂