?
Solved

MySQL or SQL -- "show tables" shows both tables and views, but I want differentiate

Posted on 2007-10-15
5
Medium Priority
?
347 Views
Last Modified: 2012-06-27
What SQL statement list only the base tables in a database, not views? And what statement
list only the views?

In MySQL, for exampel, if I use "show tables", both tables and views are displayed.


Thanks for help.
0
Comment
Question by:zzhang2006
  • 3
  • 2
5 Comments
 
LVL 6

Accepted Solution

by:
msklizmantas earned 500 total points
ID: 20080741
hi,

try:
show full tables where Table_type='BASE TABLE';

for tables
and

show full tables where Table_type='VIEW;

for views.

regards,

m
0
 
LVL 1

Author Comment

by:zzhang2006
ID: 20080822
Thanks, msklizmatas; it works.   Excuse me, one more question: Is  show full tables ..
considered as standard SQL statement or only work with MySQL meta data base?


Z
0
 
LVL 6

Expert Comment

by:msklizmantas
ID: 20080886
i don't think it is standard SQL statement, but you have reminded me that you can use information_schema "database" to query information and this "database" should be available on other database management systems.

try working with:

select * from information_schema.tables where where table_schema='<DATABASE_NAME>' and TABLE_TYPE='BASE TABLE'

it should help you to work with views also.

more information:

http://mysql.telepac.pt/doc/refman/5.0/en/information-schema.html

regards,

m

0
 
LVL 1

Author Comment

by:zzhang2006
ID: 20080994
Perfect!

Thanks


Ze
0
 
LVL 6

Expert Comment

by:msklizmantas
ID: 20081008
you are welcome ;)

m
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 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