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
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
Hi Venkat , Thank you very much for grate effort.
ReplyDeleteI 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
Sure, I will try my best to complete all concepts of MVC as soon as I can. Thank you very much for your patience.
DeleteVenkat thanks for these videos, can you please create a video list for Nunit testing ?
ReplyDelete