Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-04-27
8
Medium Priority
?
993 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
  • 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 70

Accepted Solution

by:
Scott Pletcher earned 1000 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 1000 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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

578 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