SQL SERVER Topic

List all tables in a sql server database using a query:-
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_SCHEMATABLES:- 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
  1. IT - Internal table
  2. P - Stored procedure
  3. PK - PRIMARY KEY constraint
  4. S - System table
  5. SQ - Service queue
  6. U - User table
  7. V - View