Get the list of all tables in a database using TSQL in SQL Server

Some time back I wrote an article about getting the list of all the columns in a table. Today we will see how to get the list of user tables in a database. There are several ways to get the list of all tables in a database in SQL Server. Here we will see two quick methods using TSQL metadata catalogs SYS.TABLES and INFORMATION_SCHEMA.TABLES.

Using SYS.TABLES

Sys.tables returns all the user tables in a database. Here is how to use it to list down all the tables.

USE WideWorldImporters
GO

SELECT * FROM SYS.TABLES
GO

Using INFORMATION_SCHEMA.TABLES

While using INFORMATION_SCHEMA.TABLES, you have to filter the result for TABLE_TYPE = ‘BASE TABLE’ as this metadata catalog lists table views. Here is an example.

USE WideWorldImporters
GO

SELECT * FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = 'BASE TABLE'
GO

Reference

• Read more about sys.tables at Microsoft Docs.