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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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:
|
1 |
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:
|
1 |
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. 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 🙂