SQL Script To List All Tables Present In a Database

It is a very simple script to get all tables from 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”:

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

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

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

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