Solved

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

Posted on 2014-04-27
8
851 Views
Last Modified: 2014-04-28
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?
0
Comment
Question by:dcadler
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40026284
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
 

Author Comment

by:dcadler
ID: 40026348
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
 

Author Comment

by:dcadler
ID: 40026359
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40026391
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
 

Author Comment

by:dcadler
ID: 40026433
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 40027591
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 40027996
Good point, I should have thought of that.  But they would still show up in SSMS albeit with a different schema.
0
 

Author Closing Comment

by:dcadler
ID: 40028116
That was it. They had different schema and so were showing up in SSMS with the schema first. Thanks for all your help.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question