Problem with information_schema.tables

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 2008 Forums

SQL Server Administration (2008)

Problem with information_schema.tables

Author

Topic

BruceT
Yak Posting Veteran

78 Posts

Posted - 2012-05-29 : 14:58:44

Hi,

When I run

"select * FROM information_schema.tables
WHERE table_type = 'base table' and table_schema <> 'dbo'"

I get two entries from a database that doesn't exist.
And I can't drop the users whose schemas are in that database.

dbcc checkdb on master does not show any errors.

Any suggestions on how to clean this up?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 15:19:32

hmm? how did you run this query in db if it doesnt exist in first place?
or are you telling table doesnt exist?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BruceT
Yak Posting Veteran

78 Posts

Posted - 2012-05-29 : 16:00:43

quote:
Originally posted by visakh16

hmm? how did you run this query in db if it doesnt exist in first place?
or are you telling table doesnt exist?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Let's see if I can explain better
When I run the following or the select from the information_schema.tables attached to an existing database

select * from sys.objects
WHERE name = 'tmpBillClients'
AND NOT schema_id = SCHEMA_ID ('dbo');

It comes back with two objects.

tmpBillclients 440101800 NULL 49 0 U USER_TABLE 2012-05-14 09:38:46.590 2012-05-14 09:38:46.593 0 0 0

tmpBillclients 84624536 NULL 68 0 U USER_TABLE 2012-05-25 11:46:22.653 2012-05-25 11:46:22.657 0 0 0

Neither of these user tables exists, but it seems users are being prevented from logging in to the existing database because of them.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 16:05:34

it might be that tables exists in a schema which you dont have permission to view

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BruceT
Yak Posting Veteran

78 Posts

Posted - 2012-05-29 : 16:10:49

Ah, figured it out, but seems a little odd. Logged in as SA so seems like I'd be able to seem them, but you're right. They were owned by a user schema (Drop Table user.tmpBillClients) took care of it.

Thanks visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 21:17:16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources