0 votes
1 view
in SQL by (20.3k points)

I am working on application which can deal with multiple database servers like "MySQL" and "MS SQL Server".

I want to get tables' names of a particular database using a general query which should suitable for all database types. I have tried following:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

But it is giving table names of all databases of a particular server but I want to get tables names of selected database only. How can I restrict this query to get tables of a particular database?

1 Answer

0 votes
by (40.3k points)
edited by

Try the code given below.

For MySQL:

SELECT TABLE_NAME 

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='dbName' 

Want to learn SQL from scratch? Here's is the right video for you on SQL provided by Intellipaat

For SQL Server:

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='dbName'

For Oracle, you should prefer to use DBA_TABLES.

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...