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

Text version of the video

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

Slides

All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

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.

[kirannadukula]

Hi, Venkat. Thank you, for the great videos. I have watched your videos on sqlserver. I got a job as a interface analyst. The whole credit goes to you, Sir. Thank you.

[kirannadukula]

Hi, Venkat. Thank you, for replying to the message. I am very happy now. Because, i wanted to convey to you that i got a job, as you were the only one i thought of thanking when i cleared the technical round on sqlserver. Thank you, Sir. And I have started to answer a few of the questions that the users are posting and if i don't the know the answer, i will definitely research for it and post it. Thank you, Venkat Sir. MAY GOD BLESS YOU.

[Csharp-video-tutorialsBlogspot]

Hi Krismaly, thank you very much for spending time to investigate this on sql server 2012, and letting us all know. Thanks a million. Sure, I will update the slides right away.

[bekahsierra]

You always manage to solve what a simple google search can't. Thanks.

[jfamily]

i think you are probably one heaven of a person who doesn't have any haters all over the world. kudos to u venkat.

[Csharp-video-tutorialsBlogspot]

Hi Kiran, I am very happy for you, and thank you very much for letting me know that you have got job. All the very best, and I am sure you will be successful. Please feel free to let me know if you need any help. If possible and if you have time, can you answers some of the questions that the other users of this channel has posted. I am finding it extremely difficult to answer everyone. Good luck and all the very best.

[Csharp-video-tutorialsBlogspot]

Hi David, Krismaly has checked all these on sql server 2012 and he confirmed everything works as expected.

[Csharp-video-tutorialsBlogspot]

Thank you very much Kiran, and really appreciate your help. Good luck with your new role.

[krismaly]

Dear Venkat you could update the slide with the following
1. It is available in SQL Server 2012
2. It accepts both upper and lower case for values (not a case sensitive)
Update at your own pace. No hurry.
Thanks for educating me and public

[krzysztofs]

It was needed. Very short video. For a liitle break in the course. Thank U Venkat !

[krismaly]

I call this video short and sweet. I enjoyed this video. Please move on this subject and looks very interesting. Thanks a bunch

[saagarsoni]

ohoo sir, your explaination is just to simple...
Sir can u take us more deeper into SQL...please..

[CodeRealtime]

HI venkat,

well cn't express in word's bt thnks alot for making such video . they are really very helpful .

[Lemmonio]

thank you very much for all your great videos!

[ravitejkotipalli]

Hi Venkat, your videos are very good and useful for starters who want to learn in easy and fast way.I have learned a lot from your SQL videos.
By the way can u upload SSIS, SSAS and SSRS videos also it would be very helpful for learners as well.
thank you

[wasimbader]

very informative, Thank you, everyday I learn new and more from your tutorials, One question, after completing all 135 lectures, will I be master in SQL meaning is that enough for a job if i am in Data analysis, ( also use of Excel and SAS). As I asked many lectures ago.

[davidespada]

i will check with sql server 2012 :) but i think its like sql server 2008..
Thank you grate video as usual..

[krismaly]

I think you can mention that XTYPE='P' or XTYPE='p' both works. I mean system accepts both upper and lower case.

[wasimbader]

please do Data Analysis with SQL, or other program like SAS if possible

[rekhanataraj]

Hi can u pls tell i have front end application but i dont knw in which db or table the dat ar stores