Link to home
Start Free TrialLog in
Avatar of dcadler
dcadler

asked on

why are tables missing from information_schema.tables in MS SQL database?

When I run select * from information_schema.tables, I show tables listed as base that do not show up in my list of database tables in SSMS.

Can I delete them from information_schema.tables?

Or is there a way  refresh information_schema.tables so that it is correct?
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

They are system tables.  Look in the Tables | System Tables node.

Can I delete them from information_schema.tables?
No.  That is a system VIEW.

Or is there a way  refresh information_schema.tables so that it is correct?
I suspect you will find it is correct.

Incidentally the information_schema is practically deprecated.  You would be better off using sys.Tables instead.
Avatar of dcadler
dcadler

ASKER

sys.tables still shows several tables that are not listed when I open the database in SSMS and expand the tables node. Also, when I try to run a re-indexing script that selects all of the tables  from the sys,tables or information_schema.tables, I get an error when it can't find the tables that show up in sys.tables but do not show up in SSMS. My assumption is that the tables had been deleted a while back.
Avatar of dcadler

ASKER

When you say;

Or is there a way  refresh information_schema.tables so that it is correct?
I suspect you will find it is correct.

Are you saying that the sys.tables or information_schema.tables is correct, but SSMS is not showing all of the tables in the database? I am logging in as the SA

Thanks,

Dave
sys.tables still shows several tables that are not listed when I open the database in SSMS and expand the tables node
As I stated they could be system tables or simply that you have not refreshed the tables node in SSMS.

Are you saying that the sys.tables or information_schema.tables is correct
Yes.
Avatar of dcadler

ASKER

The problem is that I can tell by the table names that they are related to the project that the database is for.

I have refreshed the SSMS, logged in and out. Those tables are just not available via SSMS or evn SQL Query. For example, I can just do a Select * from tablename and I get an "invalid object name" error.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dcadler

ASKER

That was it. They had different schema and so were showing up in SSMS with the schema first. Thanks for all your help.