List true tables only in Athena


I'd like to have a single query which lists all tables in a athena database excluding views.

There is


which returns tables as well as views. I don't want to call the latter followed by a SHOW VIEWS and then separate those in my code but have that same behavior in one single call.

How can i achieve this?

asked 2 years ago360 views
1 Answer


Instead of SHOW TABLES, how about using the information_schema table?

FROM information_schema.tables
WHERE table_schema = 'database' 

If you want to pull views separately, query the information_schema.views table.

profile pictureAWS
answered 2 years ago
  • Hi, thanks for your response. That doesn't work.

    The view in my database is listed in information_schema.tables while information_schema.views is empty. How is that possible at all?

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions