In this post, we will discuss writing a transact SQL query to list all the tables, views, store procedures in a SQL server database and it is also a very common SQL server interview question.
Within SQL server management studio, We can use Object Explorer to list all the tables that belong to a specific database.
For example, if we have to find the tables that belong to the Demo database, we can simply expand the database within object explorer and then expand the tables folder.
And then you can find the list of tables that belong to the Demo database
On the other hand, if I have to write a query to achieve the same, then we can make use of three system views.
We can either SYSOBJECTS, SYS.TABLES or INFORMATION_SCHEMA.TABLES views.
The SYSOBJECTS view is available and SQL Server 2000, 2005, and 2008.
Whereas SYS.TABLES and INFORMATION_SCHEMA.TABLES Views are available in SQL Server 2005 and 2008 and later version only, they are not available in SQL server 2000.
Now let’s see how to use SYSOBJECTS view to retrieve all the tables that are present in the Demo database.
Now, when I execute the below query, I get all the objects that are present in the Demo database. SQL Server can have different types of objects like tables, views, function stored procedures.
So SYSOBJECTS view contains all those objects. Now, if I just want the tables, then I filter on Xtype column.
When I execute the query, notice that I get the tables that are present in the Demo database.
Now Xtype, what does ‘U’ mean? ‘U’means user table. So where do I have this list? Actually, you can visit MSDN link for all possible Xtype column values and what they represent.
fn stands for a scalar function So if you want all scalar functions then you simply, filter on that
Select * from [SYSOBJECTS] where XTYPE=’fn’
if I want stored procedures, the Xtype for that is sp, I get all the stored procedures. Similarly, if you want just views then replace XTYPE with V.
Now, if I want to know what are the different types of objects are available in the Demo database, I can find out that by issuing a distinct query.
Select Distinct XTYPE from [SYSOBJECTS]
Now let’s see how to use the SYS.TABLES.
You simply say Select * from SYS.TABLES. That’s going to return all the tables in our Demo database.
Select * from SYS.TABLES
Similarly, if you want views, you can simply use, SYS.views. And if you want procedures, you simply use SYS.procedures. So I see all the stored procedures within the Demo database.
Now, let us see how to use INFORMATION_SCHEMA.TABLES View,
Select * from INFORMATION_SCHEMA.TABLES is going to give us all the tables as well as views
So if you want just the tables, then filter on the table type column.
Similarly, if you want just the views, you can use INFORMATION_SCHEMA.VIEWS.
Select * from INFORMATION_SCHEMA.VIEWS
Now, if you want stored procedures, you can use INFORMATION_SCHEMA.ROUTINES
Select * from INFORMATION_SCHEMA.ROUTINES
Thank you for reading. Have a great day.