Mysterious missing table!

Question

Post reply

Mysterious missing table!

HENRY DAVIS

SSC Rookie

Points: 37
More actions
May 9, 2008 at 1:59 pm

#154340

Strange scenario: sysobjects has a record of a user table, however that table does not exist.
select * from SYSOBJECTS where name = 'table_name'
--(1 row(s) affected)
select * from dbo.table_name
--Server: Msg 208, Level 16, State 3, Line 1
--Invalid object name 'dbo.table_name'.
How did this happen?
SQL Server 2000 Enterprise Edition version 8.00.2039 (SP4)
Windows Server 2003 Enterprise Edition

Viewing 7 posts - 1 through 7 (of 7 total)

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

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 1

Owner is probably not dbo.

What is the owner/schema of the table?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

Or the object is not a table !

create procedure dbo.MissingTable

as

select 'hello world

go

select * from SYSOBJECTS where name = 'MissingTable'

go

select * from dbo.MissingTable

go

drop procedure dbo.MissingTable

SQL = Scarcely Qualifies as a Language

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

HENRY DAVIS (5/9/2008)
Strange scenario: sysobjects has a record of a user table, however that table does not exist.
select * from SYSOBJECTS where name = 'table_name'
--(1 row(s) affected)
select * from dbo.table_name
--Server: Msg 208, Level 16, State 3, Line 1
--Invalid object name 'dbo.table_name'.
How did this happen?
SQL Server 2000 Enterprise Edition version 8.00.2039 (SP4)
Windows Server 2003 Enterprise Edition

What does the "XType" column contain when you run the following?

select * from SYSOBJECTS where name = 'table_name'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Marvin D SSCarpal Tunnel Points: 4058 More actions · Answer 4

Definately not a missing table. Remember the * bring back all objects not just tables. My money is either a Stored Proc or ufn

Marvin Dillard
Senior Consultant
Claraview Inc

HENRY DAVIS SSC Rookie Points: 37 More actions · Answer 5

both XType = 'U' and Type = 'U'

It is a table, not stored proc, not function, not view, not anything else.

I belong to sysadmin group.

Another thing, I found out the issue when updatestatistic failed.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 6

I had the exact same issue yesterday. 🙂

Run the following. I bet your schema is not "dbo".

SELECT TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'yourTableName'
AND TABLE_TYPE = 'BASE TABLE'

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]