[Resolved] Drop table but it's still in sysobjects

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

[Resolved] Drop table but it's still in sysobjects

Author

Topic

drharv
Starting Member

4 Posts

Posted - 2007-12-26 : 09:22:58

Hey all,

Not sure how I did this, but I dropped a table and it still shows up in sysobjects. This breaks a job that rebuilds indexes because the table doesn't exist. I tried recreating and dropping the table, but the extra entry is still there. Would it be okay to just delete the row from sysobjects? This has happened in a 2000 and 2005 db at the same time.

Thanks for any advice.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-26 : 09:33:54

If you want to drop a table use

Drop table tablename

and dont delete a row from sysobjects table

Madhivanan

Failing to plan is Planning to fail

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-12-26 : 09:36:40

I'd make sure the table has the proper owner. Many times when I've seen this the "phantom" table was owned by someone other than DBO.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.

drharv
Starting Member

4 Posts

Posted - 2007-12-26 : 09:37:01

Thanks for replying.

That's were my problem is. I used Drop Table, and the table is gone from the db when I look in Management Studio. I tried running Drop Table again just to be sure, but it does say the table doesn't exist. However, it is still listed in sysobjects and breaks jobs that use sysobjects to get a list of tables.

drharv
Starting Member

4 Posts

Posted - 2007-12-26 : 09:38:15

quote:
Originally posted by graz

I'd make sure the table has the proper owner. Many times when I've seen this the "phantom" table was owned by someone other than DBO.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.

It does have a different UID than the other tables. What is the proper and safe way to remove it?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-26 : 09:41:48

qualify the table name with the ownername. ie:
drop table tgb.myTable
drop table dbo.MyTable

Be One with the Optimizer
TG

drharv
Starting Member

4 Posts

Posted - 2007-12-26 : 09:49:05

Woohoo!!
That did it. It was listed at the very bottom of the table list since it was under the different user. I was able to just drop it through management studio. Now time to beat the employee that created it under his own login! lol

Thanks alot for the help everyone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources