Get tables name with the same query in Postgres-MySql-SQLite

Question

i'm working on a school project and i'm building a little server in NodeJS for our project, one of my tasks is to find a way to get the tables name of all "user"tables in the selected Database. Since i need to connect to different SQL databases, MySQL - PostgreSQL - SQLite, i was looking to get the same result by exectuing the same query, i'll explain my self. I've start working on MySQL and i've "found" this query:

`SELECT table_name FROM information_schema.tables WHERE table_schema ='${config.DB_Name}'`

And it correctly return all the table name from the selected database. After that i moved to work with PostgreSQL and that query (succesfully run) returns 0 result. So i've start looking for another query that can return the same result in Postgres, and i found this:

    SELECT table_name
  FROM information_schema.tables
 WHERE table_schema='public'
   AND table_type='BASE TABLE';

I was wondering, is there any Query which, run on MySQL-PostgreSQL-SQLite, give me the same kind of result?

No, there isn't. Check this: https://stackoverflow.com/a/83195/10498828 for SQLite. — forpas, Apr 15 '21 at 20:45
Each dialect is *substantially* different in terms of schema information. There is no magic bullet here. This is why tools like [Sequelize](https://sequelize.org) exist. — tadman, Apr 15 '21 at 21:34

score 0 · Answer 1 · answered Apr 16 '21 at 05:17

Your PostgreSQL query will work fine on all databases that support SQL standard feature F021. If it doesn't work in MySQL, then either it doesn't support that feature, or it is a bug.

Note that what PostgreSQL and the SQL standard calls a schema is called "database" in MySQL.

Get tables name with the same query in Postgres-MySql-SQLite

1 Answers1