SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Problem with information_schema.tables		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BruceT
Yak Posting Veteran

USA
77 Posts
Posted - 05/29/2012 :  14:58:44  Show Profile  Reply with Quote
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

India
48076 Posts
Posted - 05/29/2012 :  15:19:32  Show Profile  Reply with Quote
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/
Go to Top of Page

BruceT
Yak Posting Veteran

USA
77 Posts
Posted - 05/29/2012 :  16:00:43  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48076 Posts
Posted - 05/29/2012 :  16:05:34  Show Profile  Reply with Quote
it might be that tables exists in a schema which you dont have permission to view

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

BruceT
Yak Posting Veteran

USA
77 Posts
Posted - 05/29/2012 :  16:10:49  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48076 Posts
Posted - 05/29/2012 :  21:17:16  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000