CODEWITHSUNDEEP
mysql
Samuel Fullman
Samuel Fullman

Reputation: 1312

mysql tell difference between table and view

I'm surprised I didn't find this answer out there.

I know very well what the difference between a table and a view is. BUT..how do I DETERMINE whether a db object is a table or view? Since

show tables;

will show both tables and views - and there is no "show views" command.

to determine in my coding (which has to read multiple objects and may not "know" better), I do this:

show create view my_table_or_view

and if I get an error, which I prevent from dying, it's a table. Pretty clumsy, is there a better way?

Upvotes: 0

Views: 283

Answers (2)

Matt Busche
Matt Busche

Reputation: 14333

You can use the following query and if it returns a record it's a table

SELECT * 
FROM information_schema.tables 
WHERE 'TABLE_TYPE' = 'BASE TABLE'
AND table_name = 'your table name'

Upvotes: 0

splash21
splash21

Reputation: 809

try this variation instead ...

show full tables;

the Table_type column will give the info you require :)

Upvotes: 1

Related Questions