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

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

Text version of the video
http://csharp-video-tutorials.blogspot.com/2013/06/part-65-list-all-tables-in-sql-server.html

Slides
http://csharp-video-tutorials.blogspot.com/2013/09/part-65-list-all-tables-in-sql-server.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists

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

Get Paid Taking Pictures
Share