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

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' 

---

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

---

SELECT * FROM INFORMATION_SCHEMA.TABLES 

OR

SELECT * FROM Sys.Tables

---

USE YourDBName
GO 
SELECT *
FROM sys.Tables
GO

OR

USE YourDBName
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES 
GO

---

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

SQL Server 2012

---

exec sp_msforeachtable 'print ''?'''

---

select * from sysobjects where xtype='U'

---

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

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

---

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

---

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

---

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

---

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


  [1]: https://i.stack.imgur.com/o3qo9.png

https://i.stack.imgur.com/OghPy.png

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.

---

--for oracle
select tablespace_name, table_name from all_tables;

This link can provide much more information on this topic

---

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.

---

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

---

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

---

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 

---

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

---

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

---