Link for code samples used in the demo
Link for csharp, asp.net, ado.net, dotnet basics, mvc and sql server video tutorial 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.
