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'
1 – I did not write this code 🙂