SQL SERVER - How To Display all Tables Of a Database

In order to display the Names of all Tables Or Details of all Tables of a Database, We have different alternative methods.

1. Using Information_Schema : By using Information_Schema.Tables Or Information_Schema.Columns

Syntax:

• Select Table_Name From Information_Schema.Tables  Where Table_type = 'BASE TABLE'
• Select Distinct Table_Name From Information_Schema.Columns

2. Using SysObjects :

Syntax:

• Select * From Sys.Objects Where Type = 'U'
• Select * From SysObjects Where Xtype = 'U'

3. Using System Stored Procedures

Syntax:

• Exec Sp_Tables @Table_Type = "'TABLE'"
• Exec Sp_MsForEachTable 'Print ''?'''