Part 65 - List all tables in a sql server database using a query

In this we will discuss, writing a transact sql query to list all the tables in a sql server database. This is a very common sql server interview question.

Object explorer with in sql server management studio can be used to get the list of tables in a specific database. However, if we have to write a query to achieve the same, there are 3 system views that we can use.
1. SYSOBJECTS - Supported in SQL Server version 2000, 2005 & 2008
2. SYS.TABLES - Supported in SQL Server version 2005 & 2008
3. INFORMATION_SCHEMA.TABLES - Supported in SQL Server version 2005 & 2008

-- Gets the list of tables only
Select * from SYSOBJECTS where XTYPE='U'
-- Gets the list of tables only
Select * from  SYS.TABLES
-- Gets the list of tables and views
Select * from INFORMATION_SCHEMA.TABLES

To get the list of different object types (XTYPE) in a database
Select Distinct XTYPE from SYSOBJECTS

Executing the above query on my SAMPLE database returned the following values for XTYPE column from SYSOBJECTS
IT - Internal table
P - Stored procedure
PK - PRIMARY KEY constraint
S - System table 
SQ - Service queue
U - User table
V - View

Please check the following MSDN link for all possible XTYPE column values and what they represent.
http://msdn.microsoft.com/en-us/library/ms177596.aspx