Solved

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

Posted on 2014-04-27
8
758 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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:
ScottPletcher 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

919 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now