CODEWITHSUNDEEP
mysql
Yeti
Yeti

Reputation: 5818

How to get only tables, not views using SHOW TABLES?

SHOW TABLES gives you tables+views.

How do I retrieve only tables?

Upvotes: 50

Views: 27886

Answers (2)

MarcoZen
MarcoZen

Reputation: 1663

The link at https://dev.mysql.com/doc/refman/8.0/en/show-tables.html tells us that we cannot use LIKE and WHERE together ( for mysql 5.5.x - 8.x ).

So this statement WILL throw errors ( show tables which are NOT views and are further filtered by %name% );

  show full tables like "%sometablename%"  where Table_Type = 'BASE TABLE';

U will have to choose either LIKE or WHERE in one statement , not both simultaneously.

::: Solution ( requires you know the database name ( say dbName) ) :::

   show full tables where  Tables_in_dbName like "%main%" 
   and  Table_type = "Base Table";

Upvotes: 0

Francisco Soto
Francisco Soto

Reputation: 10392

show full tables where Table_Type = 'BASE TABLE'

verbatim.

Or put another way;

show full tables where Table_Type != 'VIEW'

http://dev.mysql.com/doc/refman/5.0/en/show-tables.html

Upvotes: 86

Related Questions