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?

http://stackoverflow.com/questions/465014/list-table-names, http://stackoverflow.com/questions/420741/getting-list-of-tables-and-fields-in-each-in-a-database, http://stackoverflow.com/questions/454986/is-there-a-quick-way-to-report-database-metadata-in-sql-server-2005 — DJ., Feb 13 '09 at 23:24
http://stackoverflow.com/questions/124205/how-can-i-do-the-equivalent-of-show-tables-in-t-sql — derby, Oct 06 '08 at 18:01

score 1517 · Accepted Answer · edited Oct 01 '20 at 13:29

1517

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'

edited Oct 01 '20 at 13:29

Ogglas

38,157
20
203
266

answered Oct 06 '08 at 18:00

ScottStonehouse

22,419
7
28
34

46

Please note that this will also include VIEWS, not only tables – Nathan Koop Apr 23 '12 at 15:02
18

Add the database name if you are not using the specific database so it will be SELECT TABLE_NAME FROM .INFORMATION_SCHEMA.Tables – Shriroop Aug 16 '13 at 09:28
22

Adding `WHERE TABLE_TYPE='BASE TABLE'` will include only base tables (and by extension you could always use `WHERE TABLE_TYPE != 'VIEW'`). – Phillip Copley Aug 01 '14 at 17:45
3

"sysdiagrams" appears in this list too :( – celsowm Nov 24 '14 at 17:11
4

sysdiagrams is a normal table, you always have to exclude it manually with a `AND name <> 'sysdiagrams'`. – Christoph Jun 22 '15 at 08:57
and if one wish to list only table names, one would use name in place of the * like below select name from sysobjects where xtype='U' and name ,. 'sysdiagrams' – gg89 Jul 01 '15 at 06:32
Re. the "AND TABLE_CATALOG='dbName'" form: I presume that's to avoid the `use` Statement (hard-coded or via Dynamic SQL). However, unless I first did `use dbName` (via via SSMS 2016 on SS 2008R2, 2012 and 2014), the "INFORMATION_SCHEMA.TABLES" Table would contain Rows only for the currently `use`'ed Table. – Tom Jun 05 '17 at 17:08
Re. `xtype='U'` to exclude non-user created Tables: When I tried it via SSMS 2016 on SS 2008R2, 2012 and 2014, `xtype='U'` also on non-(*explicitly*) user-created Tables with the following prefixes: "sys", "MSpeer_", "MSpub_identity_range", "MSreplication_", "MSsubscription_, "MSsnapshotdeliveryprogress" and there appeared to be no other Column on either `INFORMATION_SCHEMA.TABLES` or `SYSOBJECTS` Tables that could be used to differentiate. – Tom Jun 05 '17 at 17:43
select name from sys.tables – Michal B. Oct 16 '18 at 08:11

score 200 · Answer 2 · edited Oct 27 '15 at 18:10

200

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

edited Oct 27 '15 at 18:10

th3byrdm4n

164
1
9

answered Oct 06 '08 at 18:02

Micah

101,237
81
221
320

9

The aliasing is a bit redundant: `SELECT name FROM sysobjects WHERE xtype = 'U'` would do the same thing. – PJSCopeland Jun 30 '15 at 03:24
Thanks, initially i tried this with multiple select statements for `PK,FK,D,C,V,UQ` etc to compare source and target database, but then i found [this](http://stackoverflow.com/a/685073/2218697) feature in VS, but is there not a `sql query` to compare complete source and target database ? – Shaiju T Dec 03 '15 at 11:29
One wonders why `'U'`is used to identify the User Table... as opposed to maybe `'UT'` or, the most intuitive, `'T'`...Ah well, this works! – user919426 Mar 02 '17 at 04:51

score 93 · Answer 3 · edited Apr 19 '20 at 17:14

93

SELECT * FROM INFORMATION_SCHEMA.TABLES

OR

SELECT * FROM Sys.Tables

edited Apr 19 '20 at 17:14

Arsen Khachaturyan

6,472
4
32
36

answered Oct 06 '08 at 17:58

StingyJack

17,990
7
58
115

5

Just a note that (as mentioned in other answers) sys.tables is only available in 2005 onwards – Rob Oct 06 '08 at 18:03
4

Which is not a problem in 2018. I think this should be higher :-) – Michal B. Oct 16 '18 at 07:55

Vikash Singh · Answer 4 · 2019-01-29T02:03:26.857

31

USE YourDBName
GO 
SELECT *
FROM sys.Tables
GO

OR

USE YourDBName
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES 
GO

edited Jan 29 '19 at 02:03

answered Oct 03 '13 at 11:23

Vikash Singh

774
1
9
10

score 11 · Answer 5 · answered Aug 13 '13 at 07:45

11

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

SQL Server 2012

answered Aug 13 '13 at 07:45

Rasoul Zabihi

2,115
2
15
10

score 9 · Answer 6 · answered Oct 06 '08 at 17:53

9

exec sp_msforeachtable 'print ''?'''

answered Oct 06 '08 at 17:53

Ray

169,974
95
213
200

score 9 · Answer 7 · edited Mar 15 '12 at 02:21

9

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

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

edited Mar 15 '12 at 02:21

Nick Chammas

9,813
7
49
105

answered Oct 06 '08 at 17:55

devio

35,442
6
73
138

score 8 · Answer 8 · answered Oct 06 '08 at 17:55

8

select * from sysobjects where xtype='U'

answered Oct 06 '08 at 17:55

spoulson

20,523
14
72
101

score 6 · Answer 9 · answered Oct 06 '08 at 17:56

6

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

answered Oct 06 '08 at 17:56

Erikk Ross

2,157
13
18

`SELECT name FROM sysobjects WHERE xtype='U' AND name <> 'sysdiagrams';` because the sysdiagrams table although created by Microsoft SQL Server Management Studio is technically not a system table but one we usually like to exclude anyway. – Christoph Jun 22 '15 at 08:42

score 5 · Answer 10 · answered Sep 27 '17 at 12:20

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

score 3 · Answer 11 · answered Feb 16 '19 at 09:00

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

score 2 · Answer 12 · answered Nov 13 '15 at 12:34

2

--for oracle
select tablespace_name, table_name from all_tables;

This link can provide much more information on this topic

answered Nov 13 '15 at 12:34

Demietra95

226
1
2
9

4

This is not for SQL Server, so is not an answer to this question. – Dan Getz Jan 15 '16 at 19:59

score 2 · Answer 13 · answered Oct 13 '17 at 03:10

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.

score 2 · Answer 14 · edited May 07 '18 at 12:08

2

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

edited May 07 '18 at 12:08

EstevaoLuis

1,902
7
28
35

answered Nov 23 '17 at 17:28

Vikash

51
3

score 1 · Answer 15 · answered Jul 07 '17 at 04:35

1

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

answered Jul 07 '17 at 04:35

DmitryBoyko

32,983
69
281
458

score 1 · Answer 16 · answered Aug 17 '17 at 22:19

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

score 1 · Answer 17 · answered Jan 07 '19 at 07:43

1

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

answered Jan 07 '19 at 07:43

Masoud Darvishian

3,080
4
27
37

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

17 Answers17

Linked

Related