How do I get list of all tables in a database usin

问题:

What is the best way to get the names of all of the tables in a specific database on SQL Server?

回答1:

SQL Server 2005, 2008, 2012, 2014 or 2016:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=\'BASE TABLE\'

To show only tables from a particular database

SELECT TABLE_NAME 
FROM <DATABASE_NAME>.INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = \'BASE TABLE\'

Or,

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = \'BASE TABLE\' 
    AND TABLE_CATALOG=\'dbName\' --(for MySql, use: TABLE_SCHEMA=\'dbName\' )

PS: For SQL Server 2000:

SELECT * FROM sysobjects WHERE xtype=\'U\' 

回答2:

SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = \'U\'

Here is a list of other object types you can search for as well:

• AF: Aggregate function (CLR)
• C: CHECK constraint
• D: Default or DEFAULT constraint
• F: FOREIGN KEY constraint
• L: Log
• FN: Scalar function
• FS: Assembly (CLR) scalar-function
• FT: Assembly (CLR) table-valued function
• IF: In-lined table-function
• IT: Internal table
• P: Stored procedure
• PC: Assembly (CLR) stored-procedure
• PK: PRIMARY KEY constraint (type is K)
• RF: Replication filter stored procedure
• S: System table
• SN: Synonym
• SQ: Service queue
• TA: Assembly (CLR) DML trigger
• TF: Table function
• TR: SQL DML Trigger
• TT: Table type
• U: User table
• UQ: UNIQUE constraint (type is K)
• V: View
• X: Extended stored procedure

回答3:

SELECT * FROM INFORMATION_SCHEMA.TABLES 

or

SELECT * FROM Sys.Tables

回答4:

select * from sys.tables;

OR

SELECT * FROM INFORMATION_SCHEMA.TABLES 

OR

SELECT * FROM sysobjects WHERE xtype=\'U\'

回答5:

USE YourDBName
GO 
SELECT *
FROM sys.Tables
GO

回答6:

exec sp_msforeachtable \'print \'\'?\'\'\'

回答7:

SELECT * FROM information_schema.tables
where TABLE_TYPE = \'BASE TABLE\'

SQL Server 2012

回答8:

SELECT name 
FROM sysobjects 
WHERE xtype=\'U\' 
ORDER BY name;

(SQL Server 2000 standard; still supported in SQL Server 2005.)

回答9:

select * from sysobjects where xtype=\'U\'

回答10:

SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = \'U\' 

回答11:

The downside of INFORMATION_SCHEMA.TABLES is that it also includes system tables such as dtproperties and the MSpeer_... tables, with no way to tell them apart from your own tables.

I would recommend using sys.objects (the new version of the deprecated sysobjects view), which does support excluding the system tables:

select *
from sys.objects
where type = \'U\'      -- User tables
and is_ms_shipped = 0 -- Exclude system tables

回答12:

In SSMS, to get all fully qualified table names in a specific database (E.g., \"MyDatabase\"):

SELECT [TABLE_CATALOG] + \'.\' + [TABLE_SCHEMA] + \'.\' + [TABLE_NAME]
FROM   MyDatabase.INFORMATION_SCHEMA.Tables
WHERE  [TABLE_TYPE] = \'BASE TABLE\' and [TABLE_NAME] <> \'sysdiagrams\'
ORDER BY [TABLE_SCHEMA], [TABLE_NAME]

Results:

• MyDatabase.dbo.MyTable1
• MyDatabase.dbo.MyTable2
• MyDatabase.MySchema.MyTable3
• MyDatabase.MySchema.MyTable4
• etc.

回答13:

Please use this. You will get table names along with schema names:

SELECT SYSSCHEMA.NAME, SYSTABLE.NAME
FROM SYS.tables SYSTABLE
INNER JOIN SYS.SCHEMAS SYSSCHEMA
ON SYSTABLE.SCHEMA_ID = SYSSCHEMA.SCHEMA_ID

回答14:

you can simply select your database first

use database_name;

then just type

show tables;

回答15:

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

回答16:

Thanks to Ray Vega, whose response gives all user tables in a database...

exec sp_msforeachtable \'print \'\'?\'\'\'

sp_helptext shows the underlying query, which summarises to...

select * from dbo.sysobjects o 
join sys.all_objects syso on o.id =  syso.object_id  
where OBJECTPROPERTY(o.id, \'IsUserTable\') = 1 
and o.category & 2 = 0 

回答17:

--for oracle
select tablespace_name, table_name from all_tables;

---

This link can provide much more information on this topic