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

Question

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

Answer 1

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

Answer 2

select * from sys.tables;

OR

SELECT * FROM INFORMATION_SCHEMA.TABLES 

OR

SELECT * FROM sysobjects WHERE xtype='U'

Answer 3

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

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

Answer 4

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.

Answer 5

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' 

Answer 6

USE YourDBName
GO 
SELECT *
FROM sys.Tables
GO