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
List Of All Tables In A Database using SYS.TABLES

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
List Of All Tables In A Database using INFORMATION_SCHEMA.TABLES

Reference


1 thought on “Get the list of all tables in a database using TSQL in SQL Server”

  1. Thanks for the helpful article. For those interested in this topic, I recommend reading this article (https:// www. devart .com /dbforge/sql/studio/show-tables-in-sql-server-database.html) about an alternative approach to show tables – utilize GUI tools like Microsoft’s SQL Server Management Studio (SSMS) or dbForge Studio for SQL Server.

    Reply

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.