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

sql-server tsql database-table
Ray picture Ray · Oct 6, 2008 · Viewed 1.8M times · Source

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

Answer

ScottStonehouse picture ScottStonehouse · Oct 6, 2008

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'

Related questions

Count the Number of Tables in a SQL Server Database

I have a SQL Server 2012 database called MyDatabase. How can I find how many tables are in the database? I'm assuming the format of the query would be something like the following, but I don't know what to replace database_…

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More