Get all table names of a particular database by SQL query?

Question

I 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?

Answer 1

try this:

USE DBName
GO 
SELECT *
FROM sys.Tables
GO

Answer 2

Stolen from here:

USE YOURDBNAME
GO 
SELECT *
FROM sys.Tables
GO

Answer 3

Probably due to the way different sql dbms deal with schemas.

Try the following

For MySQL

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

For MS SQL

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

For Oracle I think the equivalent would be to use DBA_TABLES