13

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?

  • 4
    Try putting brackets around the table name, same response? – rvsc48 Nov 13 '17 at 18:05
  • 1
    Can you try using the QUOTENAME function if @rvsc48's solution doesn't do it (although I think it will). docs.microsoft.com/en-us/sql/t-sql/functions/… – MguerraTorres Nov 13 '17 at 18:06
  • 1
    Brackets: same response. – that it guy Nov 13 '17 at 18:14
  • 1
    Please run the following query in the DB containing this table and paste the output into the question: SELECT [name], CONVERT(VARBINARY(128), [name]) FROM sys.tables WHERE [name] = N'#OBSOLETE';. Thanks. – Solomon Rutzky Nov 13 '17 at 18:24
  • 2
    Another option (that I don't have time to test): 1) Get the object_id of that table. 2) Restart the instance in single-user mode. 3) connect via Dedicated Admin Connection. 4) In that DB, try something like UPDATE sys.objects$ SET [name] =N'obsolete' WHERE [object_id] = {ye_olde_object_id}; {enter} GO {enter}. Worth a shot.. – Solomon Rutzky Nov 13 '17 at 19:43
16

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."

  • 4
    I like the answer, but maybe it's worth adding a sentence as to why this is so dangerous to do? – Joe Obbish Nov 13 '17 at 23:52
  • @JoeObbish Thanks, and good suggestion. I will try to add something tomorrow. – Solomon Rutzky Nov 14 '17 at 4:15
  • 2
    @JoeObbish I just added something. Is that sufficient? – Solomon Rutzky Nov 14 '17 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.