Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
374 views
in Technique[技术] by (71.8m points)

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

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

(我正在研究可以处理多个数据库服务器(如“ MySQL”和“ 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?

(如何限制此查询以获取特定数据库的表?)

  ask by Awan translate from so

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

(可能是由于不同的sql dbms处理架构的方式所致。)

Try the following

(尝试以下)

For SQL Server:

(对于SQL Server:)

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

For MySQL:

(对于MySQL:)

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

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

(对于Oracle,我认为等效方法是使用DBA_TABLES 。)


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...