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?
dcadlerAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Are you including the schema name, or just the table name?  Most likely the tables are in a different schema; developers often accidentally create tables in a schema other than 'dbo'.
0
 
Anthony PerkinsCommented:
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.
0
 
dcadlerAuthor Commented:
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.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
dcadlerAuthor Commented:
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
0
 
Anthony PerkinsCommented:
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.
0
 
dcadlerAuthor Commented:
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.
0
 
Anthony PerkinsConnect With a Mentor Commented:
Good point, I should have thought of that.  But they would still show up in SSMS albeit with a different schema.
0
 
dcadlerAuthor Commented:
That was it. They had different schema and so were showing up in SSMS with the schema first. Thanks for all your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.