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.

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:

 DECLARE
    @owner_name     sysname
  , @table_name     sysname      
  , @index_id       int      
  , @index_name     sysname      
  , @exec_string    varchar(4000)

DECLARE tab_idxs_cursor INSENSITIVE CURSOR FOR
 SELECT object_schema_name(so.id), so.name, si.name, si.indid
   FROM sys.sysindexes si
      , sys.sysobjects so
    WHERE so.id > 100
      AND so.type = 'U'
      AND so.id = si.id
      AND si.status & 64 = 0
      AND si.indid BETWEEN 1 and 254
   ORDER BY object_schema_name(so.id), so.name, si.indid

 OPEN tab_idxs_cursor

 FETCH tab_idxs_cursor INTO @owner_name, @table_name, @index_name, @index_id
 WHILE (@@FETCH_STATUS = 0)
  BEGIN
   SELECT @exec_string = 'DBCC SHOWCONTIG( ''' + @owner_name + '.' + @table_name + ''' , '''+ @index_name + ''') WITH TABLERESULTS'

   EXEC ( @exec_string )
    
   FETCH tab_idxs_cursor INTO @owner_name, @table_name, @index_name, @index_id
  END

 CLOSE tab_idxs_cursor
 DEALLOCATE tab_idxs_cursor

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:

SELECT @exec_string = 'DBCC SHOWCONTIG( ''' + QUOTENAME(@owner_name) + '.' + QUOTENAME(@table_name) + ''' , '''+ QUOTENAME(@index_name) + ''') WITH TABLERESULTS'

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:

SELECT @exec_string = 'DBCC SHOWCONTIG( ''' + QUOTENAME(@owner_name) + '.' + QUOTENAME(@table_name) + ''' , '''+ @index_name + ''') WITH TABLERESULTS'

Having said that, I certainly don’t recommend using the deprecated DBCC SHOWCONTIG, you should use the sys.dm_db_index_physical_stats DMV to inspect index fragmentation.


1 – I did not write this code 🙂