Query all tables in database

Get all table names of a particular database by SQL query?
April 30, 2023 – 08:51 am
Browsing Hive tables data and metadata is getting faster and

USE dbName; SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_SCHEMA = 'dbName' OR TABLE_SCHEMA = 'schemaName') ORDER BY TABLE_NAME

If you are working with multiple schemata on an MS SQL server, then SELECT-ing TABLE_NAME without also simultaneously selecting TABLE_SCHEMA might be of limited benefit, so I have assumed we are interested in the tables belonging to a known schema when using MS SQL Server.

I have tested the query above with SQL Server Management Studio using an SQL Server database of mine and with MySQL Workbench using a MySQL database, and in both cases it gives the table names.

The query bodges Michael Baylon's two different queries into one that can then run on either database type. The first part of the WHERE clause works on MySQL databases and the second part (after the OR) works on MS SQL Server databases. It is ugly and logically a little incorrect as it supposes that there is no undesired schema with the same name as the database. This might help someone who is looking for one single query that can run on either database server.

Source: stackoverflow.com
Related Posts