Table in INFORMATION_SCHEMA.TABLES missing

Question

Post reply

Table in INFORMATION_SCHEMA.TABLES missing

dan-572483

SSCoach

Points: 15870
More actions
June 11, 2012 at 10:36 am

#262262

We have a maintanance stored procudure what has been failing with an error saying that a table was invalid. I looked for the table and sure enough it wasn't there I looked at the code in the AP and found the following:
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FRAGResultTable')
CREATE TABLE FRAGResultTable ...
Well, if the table is not there, It's supposed to create it right? Except that it's not really checking if the table is there. It's checking if it is listed in INFORMATION_SCHEMA.TABLES. Sure enough, it was there, but but there was no actual table, so when the table that is not recreated is referenced, the sp fails.
Fixing this issue was simple enough, I just ran the CREATE TABLE code above (and again for a similar missing table) and the sp runs. But I'm concened that INFORMATION_SCHEMA.TABLES may still be out-of-sync with what actually exists. This DB seems to be passing DBCC CHECKDB. Is there another DBCC command or other way to verify that INFORMATION_SCHEMA.TABLES matches the tables that actually exist?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

could the table exist under a different user/schema? so instead of dbo.FRAGResultTable it's Bob.FRAGResultTable?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dan-572483 SSCoach Points: 15870 More actions · Answer 2

That should be apparent when running

select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'FRAGResultTable'

Shoudn't it?

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

Information schema views are based off the underlying system tables, there's no lag and they cannot get out of sync (well not without the database being considered seriously corrupt)

My guess, the table does exist in a different schema. Rather use sys.tables than the information_schema and check the schema as well.

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

Lowell SSC Guru Points: 323518 More actions · Answer 4

dan-572483 (6/11/2012)
That should be apparent when running
select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'FRAGResultTable'
Shoudn't it?

not necessarily;it depends on the user context the preocdure is running under, i think.

if you don't have access to the "bob" schema, it will not show up in sys.tables or in information_schema.Tables, either, right?

those views are filtered based on your personal permissions...sa/sysadmin sees all, but not a regular user.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dan-572483 SSCoach Points: 15870 More actions · Answer 5

That was it. It turns out that when the maintanance job was running, it detected the table in one schema, thereby bypassing the create statement, then attempted to run inserts on the table in a different schema. I missed that initially because the logon I was using on SSMS did not have rights to see the other (erronious) schema.

Dan