How do I get list of all tables in a database using TSQL?

Question

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

Answer 1

SQL Server 2000, 2005, 2008, 2012, 2014, 2016, 2017 or 2019:

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 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

Answer 3

SELECT * FROM INFORMATION_SCHEMA.TABLES 

OR

SELECT * FROM Sys.Tables

Answer 4

USE YourDBName
GO 
SELECT *
FROM sys.Tables
GO

OR

USE YourDBName
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES 
GO

Answer 5

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

SQL Server 2012

Answer 6

exec sp_msforeachtable 'print ''?'''

Answer 7

select * from sysobjects where xtype='U'

Answer 8

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

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

Answer 9

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

Answer 10

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

Answer 11

Well you can use sys.objects to get all database objects.

 GO
 select * from sys.objects where type_desc='USER_TABLE' order by name
 GO

OR

--  For all tables
select * from INFORMATION_SCHEMA.TABLES 
GO 

  --- For user defined tables
select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE'
GO

  --- For Views
select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='VIEW'
GO

Answer 12

Any of the T-SQL code below will work in SQL Server 2019:

-- here, you need to prefix the database name in INFORMATION_SCHEMA.TABLES
SELECT TABLE_NAME FROM [MSSQL-TEST].INFORMATION_SCHEMA.TABLES;

-- The next 2 ways will require you to point
-- to the specific database you want to list the tables

USE [MSSQL-TEST];
-- (1) Using sys.tables
SELECT * FROM sys.tables;

-- (2) Using sysobjects
SELECT * FROM sysobjects
WHERE type='U';

Here's a working example using [Skyvia] using sys.tables.

[Skyvia] should be the link to https://skyvia.com/connectors/sql-server

Your SQL GUI tool should also have a way to list down all the tables in a database like the one above.

So, whatever suits your need and taste, there’s a code or GUI tool for that.

Answer 13

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 14

UPDATE 2022: You can list/show the tables that you created with this simple query in Microsoft SQL SERVER.

select * from SYS.TABLES;

Answer 15

--for oracle
select tablespace_name, table_name from all_tables;

---

This link can provide much more information on this topic

Answer 16

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

Answer 17

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

Answer 18

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 

Answer 19

Using SELECT * FROM INFORMATION_SCHEMA.COLUMNS also shows you all tables and related columns.

Answer 20

To remove tables added by replication and any other table Microsoft adds run this:

SELECT s.NAME SchemaName, t.NAME TableName
FROM [dbname].SYS.tables t
INNER JOIN [dbname].SYS.SCHEMAS s
ON t.SCHEMA_ID = s.SCHEMA_ID
WHERE t.is_ms_shipped=0 and type_desc = 'USER_TABLE'
ORDER BY s.NAME, t.NAME