Remove item in sysobjects because it is no longer in database

Question

Post reply

Remove item in sysobjects because it is no longer in database

K Currie

SSCommitted

Points: 1662
More actions
August 16, 2013 at 10:44 am

#277047

I have an item in sysobjects that is no longer displaying in my database. (Possible corruption, I'm not sure.)
As a result, I'd like to remove this item. When I run the following query, I get an error and when I try to allow ad hoc updates, I still get an error. How can I remove this item from sysobjects?
delete from sysobjects
where name = 'temptest'
Error:
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
sp_configure 'allow updates', 1
GO
RECONFIGURE;
GO
Error:
Msg 5808, Level 16, State 1, Line 1
Ad hoc update to system catalogs is not supported.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

arnipetursson SSCertifiable Points: 6557 More actions · Answer 1

sp_configure 'allow updates', 1 is no longer supported, as you found out.

Have you run DBCC on the database in question?

arnipetursson SSCertifiable Points: 6557 More actions · Answer 2

What happens if you explicitly try to drop the object via T-SQL?

K Currie SSCommitted Points: 1662 More actions · Answer 3

No problems with CheckDB. Is there another DBCC I should be running?:

DBCC checkdb

CHECKDB found 0 allocation errors and 0 consistency errors in database 'XXXX'.

And Can't Drob Table Via SQL...

drop table temptest

Msg 3701, Level 11, State 5, Line 2

Cannot drop the table 'temptest', because it does not exist or you do not have permission.

arnipetursson SSCertifiable Points: 6557 More actions · Answer 4

What schema is the table in?

What is the output of select * from sys.objects where name = 'temptest'

K Currie SSCommitted Points: 1662 More actions · Answer 5

I'm not sure if I'm providing the right result.

select schema_ver, stats_schema_ver,* from sysobjects

where name = 'temptest'

Gives me zero for both columns.

arnipetursson SSCertifiable Points: 6557 More actions · Answer 6

arnipetursson

SSCertifiable

Points: 6557

August 16, 2013 at 11:32 am

#1642284

select * from sys.objects

not sysobjects

K Currie SSCommitted Points: 1662 More actions · Answer 7

Doh!

select schema_id, is_schema_published,* from sys.objects

where name = 'temptest'

Results: 55 & 0

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Unless it's causing trouble (failed checkDB or other errors), just leave it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arnipetursson SSCertifiable Points: 6557 More actions · Answer 9

drop table <schema_name>.temptest

<schema_name> is what you find in sys.schemas where schema_id = 55.

the lesson here is ALWAYS use two part name (schema_name.object_name)

K Currie SSCommitted Points: 1662 More actions · Answer 10

Ah, that worked. Not sure how it remained in sysobjects but it is gone now.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 11

K Currie (8/16/2013)
Ah, that worked. Not sure how it remained in sysobjects but it is gone now.

It remained in sysobjects, because it existed!

Beware that sysobjects is a compatibility view, and may not display all information about an object. For instance sysobjects would have told you if the object had a different owner than dbo through the uid column. But since sysobjects is designed from the assumption that schema and owner are the same thing, it does not show the schema separately.

Make sure that you always use sys.objects, sys.columns etc.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]