通过 SQL 查询获取特定数据库的所有表名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3913620/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get all table names of a particular database by SQL query?
提问by Awan
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?
但它给出了特定服务器的所有数据库的表名，但我只想获取所选数据库的表名。如何限制此查询以获取特定数据库的表？
回答by Michael Baylon
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.
回答by anishMarokey
The following query will select all of the Tablesin the database named DBName:
以下查询将选择Tables名为 的数据库中的所有内容DBName：
USE DBName
GO
SELECT *
FROM sys.Tables
GO
回答by Gopal00005
USE DBName;
SELECT * FROM sys.Tables;
We can deal without GOin-place of you can use semicolon ;.
我们可以不用GO就地处理，你可以使用分号;。
回答by David S
Just put the DATABASE NAMEin front of INFORMATION_SCHEMA.TABLES:
只需将 放在DATABASE NAME前面INFORMATION_SCHEMA.TABLES：
select table_name from YOUR_DATABASE.INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'
回答by Lorenzo Lerate
In mysql, use:
在 mysql 中，使用：
SHOW TABLES;
After selecting the DB with:
选择数据库后：
USE db_name
回答by Dario Cimmino
I did not see this answer but hey this is what I do :
我没有看到这个答案，但嘿，这就是我所做的：
SELECT name FROM databaseName.sys.Tables;
回答by Ashish Gupta
For Mysql you can do simple. SHOW TABLES;
对于Mysql，你可以做的很简单。 显示表；
回答by amanda
select * from sys.tables
order by schema_id --comments: order by 'schema_id' to get the 'tables' in 'object explorer order'
go
回答by Ivan
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.
如果您在 MS SQL 服务器上使用多个模式，那么在不同时选择 TABLE_SCHEMA 的情况下选择 TABLE_NAME 的好处可能有限，因此我假设我们在使用 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.
我已经使用 SQL Server Management Studio 使用我的 SQL Server 数据库和使用 MySQL 数据库的 MySQL Workbench 测试了上面的查询，在这两种情况下，它都给出了表名。
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.
该查询将 Michael Baylon 的两个不同查询合并为一个，然后可以在任一数据库类型上运行。WHERE 子句的第一部分适用于 MySQL 数据库，第二部分（在 OR 之后）适用于 MS SQL Server 数据库。它是丑陋的并且在逻辑上有点不正确，因为它假设没有与数据库同名的不需要的模式。这可能对正在寻找可以在任一数据库服务器上运行的单个查询的人有所帮助。
