使用SQL查询获取特定数据库的所有表名?

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?

但是它提供了一个特定服务器的所有数据库的表名，但是我只想获取所选数据库的表名。如何限制该查询以获取特定数据库的表?

13 个解决方案

#1

345

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

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

Try the following

试试以下

For SQL Server:

SQL服务器:

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。

#2

Stolen from here:

偷来的:

USE YOURDBNAME
GO 
SELECT *
FROM sys.Tables
GO

#3

The following query will select all of the Tables in the database named DBName:

下面的查询将选择数据库中名为DBName的所有表:

USE DBName
GO 
SELECT *
FROM sys.Tables
GO

#4

USE DBName;
SELECT * FROM sys.Tables;

We can deal without GO in-place of you can use semicolon ;.

我们可以在不需要的情况下进行交易，可以使用分号;

#5

Just put the DATABASE NAME in front of INFORMATION_SCHEMA.TABLES:

只需将数据库名称放在information_schema前面。

select table_name from YOUR_DATABASE.INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'

#6

In mysql, use:

在mysql中,使用:

SHOW TABLES;

After selecting the DB with:

选择DB后:

USE db_name

#7

I did not see this answer but hey this is what I do :

我没有看到这个答案，但这就是我所做的:

SELECT name FROM databaseName.sys.Tables;

#8

For Mysql you can do simple. SHOW TABLES;

对于Mysql，你可以做的很简单。显示表;

#9

Exec sp_MSforeachtable 'Select ''?'''

#10

select * from sys.tables
order by schema_id      --comments: order by 'schema_id' to get the 'tables' in 'object explorer order'
go

#11

Yes oracle is :

是的甲骨文是:

select * from user_tables

That is if you only want objects owned by the logged in user/schema otherwise you can use all_tables or dba_tables which includes system tables.

也就是说，如果您只想要由登录的用户/模式拥有的对象，则可以使用包含系统表的all_tables或dba_tables。

#12

Building from Michael Baylon's answer, I needed a list which also included schema information and this is how I modified his query.

从Michael Baylon的回答中，我需要一个包含模式信息的列表，这是我修改他的查询的方式。

SELECT TABLE_SCHEMA + '.' + TABLE_NAME as 'Schema.Table'
  FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'dbName'
  ORDER BY TABLE_SCHEMA, TABLE_NAME

#13

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服务器上使用多个schemata，那么选择TABLE_NAME而不同时选择TABLE_SCHEMA可能会带来有限的好处，因此我假定我们对使用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数据库和MySQL工作台使用MySQL数据库对上面的查询进行了测试，并且在这两种情况下都给出了表名。

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数据库上工作，第二部分(在或之后)在MS SQL Server数据库上工作。它是丑陋的，逻辑上有点不正确，因为它假设没有一个与数据库同名的不需要的模式。这可能有助于寻找可以在任何数据库服务器上运行的单个查询的人。

#1

345