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

The best answers to the question “How do I get list of all tables in a database using TSQL?” in the category Dev.

QUESTION:

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

ANSWER:

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:

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:

USE YourDBName
GO 
SELECT *
FROM sys.Tables
GO

OR

USE YourDBName
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES 
GO

ANSWER:

SELECT * FROM INFORMATION_SCHEMA.TABLES 

OR

SELECT * FROM Sys.Tables