通过 SQL 查询获取特定数据库的所有表名? [英] Get all table names of a particular database by SQL query?

问题描述

我正在开发可以处理多个数据库服务器(如MySQL")的应用程序.和MS SQL Server".

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?

推荐答案

可能是由于不同的 sql dbms 处理模式的方式.

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

试试下面的方法

对于 SQL Server:

For SQL Server:

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

对于 MySQL:

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

对于 Oracle，我认为等效的方法是使用 DBA_TABLES.

For Oracle I think the equivalent would be to use DBA_TABLES.

这篇关于通过 SQL 查询获取特定数据库的所有表名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！