Advertisement
Advertisement


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?

2013/12/07
1
908
12/7/2013 2:36:10 AM

Accepted Answer

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' 
2020/04/23
1455
4/23/2020 8:18:09 PM

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
2015/10/27

SELECT * FROM INFORMATION_SCHEMA.TABLES 

OR

SELECT * FROM Sys.Tables
2020/04/19

USE YourDBName
GO 
SELECT *
FROM sys.Tables
GO

OR

USE YourDBName
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES 
GO
2019/01/29

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

SQL Server 2012

2013/08/13

exec sp_msforeachtable 'print ''?'''
2008/10/06

Source: https://stackoverflow.com/questions/175415
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]