SQL Script To List All Tables Present In a Database

Friends,

In this small post, we will see 2 different ways of retrieving all the tables from a specific database. It is a very simple script to get all tables from a database. We can do it using 2 ways. They are as below –

• Using INFORMATION_SCHEMA.TABLES table
• Using sys.Tables table

The below script lists all tables in the database named “MyDB”:

USE MyDB
GO
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_NAME

The below script uses sys.Tables table to achieve the same:

USE MyDB
GO
SELECT * FROM sys.Tables ORDER BY name

Hope this helps you! Keep learning and sharing! Cheers!