Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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

Suggested Videos
Part 62 - What to choose for performance - SubQuery or Joins
Part 63 - Cursors in sql server
Part 64 - Replacing cursors using joins in sql server

In this video 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

2 comments:

  1. Hi Venkat , Thank you very much for grate effort.
    I think its one stop solution for All Microsoft Technologies.It helps a lot for the community.
    i think mvc part is not completed right?
    I am requesting you that please complete mvc part
    by covering all the stuff why because more opportunities are there in the market on this technology. It always better to grab the opportunity as early as possible.I know you need to balance your work ,recording videos and also need to look after your family,If possible try to complete with covring all the topics as early as possible
    thanks a ton venkat

    ReplyDelete
    Replies
    1. Sure, I will try my best to complete all concepts of MVC as soon as I can. Thank you very much for your patience.

      Delete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.