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?
Can I delete them from information_schema.tables?
Or is there a way refresh information_schema.tables so that it is correct?
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.
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
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.
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That was it. They had different schema and so were showing up in SSMS with the schema first. Thanks for all your help.
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.