SQL Server – Table Name Starting with # in User Database, Not Tempdb

sql serversql-server-2008-r2temporary-tables

Somehow, decades ago, a table got created in our database that starts with a #. It shows up in Object Explorer under the app's database, not in tempdb. For some reason, Azure won't import the database like this.

We can't drop it, rename it, or interact with it. I've tried Delete from Object Explorer, Script Drop, Rename from the GUI and none of them have worked.

We're on SQL 2008 R2.

drop table [*app*].[dbo]."#OBSOLETE";

Database name '*app*' ignored, referencing object in tempdb.
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#OBSOLETE', because it does not exist or you do not 
have permission.

exec sp_rename "dbo.#OBSOLETE", "dbo.obsolete"

Msg 15225, Level 11, State 1, Procedure sp_rename, Line 338
No item by the name of 'dbo.#OBSOLETE' could be found in the current database '*app*', given that @itemtype was input as '(null)'.

How do we kill this object so we can migrate this to Azure?

Best Answer

Given:

  1. sp_rename uses object names instead of object IDs,
  2. we cannot use the object name since it starts with a # and that is interpreted as having special meaning and is handled differently,
  3. all other options have been exhausted

You should try editing the underlying system catalog table directly via a Dedicated Admin Console (DAC) connection:

  1. Get the object_id of that table.
  2. Restart the instance in Single-User mode. This is to be able to directly update the system tables (i.e. not a requirement for using the DAC connection).

  3. Connect via Dedicated Admin Console connection. You can do this in a SQLCMD interactive session by running the following in a Command Prompt window:

    C:\> SQLCMD -A -E

    or, connect directly to the DB using:

    C:\> SQLCMD -A -E -d {database_name}

  4. In that DB, try something like the following:

    UPDATE sys.objects$ {enter}
SET [name] = N'obsolete' {enter}
WHERE [object_id] = {ye_olde_object_id}; {enter}
GO {enter}

    It won't execute the statement until you put in the GO {enter}.

Please be cautious when directly editing system catalog tables, and do not get too comfortable with the idea of doing it. This is something that should be done only if there is absolutely no other way of fixing a problem (such as the case here). There are probably several reasons to avoid making direct edits, but the two that initially come to mind are:

  • Much like the data-models that we create, there are likely rules and workflows for how things work that we are unaware of (e.g. denormalization, "business" rules governing the state of data across various tables, etc)

  • Quite likely making direct edits voids Microsoft's responsibility to assist you if you run into problems and have a support contract (I have not seen the terms of the Support agreement(s), but I have a hard time believing that such language would not be in there)

    @Paul Randal confirmed in a comment on a related answer of mine: "manually editing a system table irrevocably sets a flag in the boot page of the database that marks your database as having been edited in this way, and CSS might decide to not help you if you subsequently have problems with that database."

Related Question