In this post, we will discuss writing a Transact-SQL query to list all the tables, views, and stored procedures in a SQL Server database. This 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 need 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.
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 use either SYSOBJECTS, SYS.TABLES, or INFORMATION_SCHEMA.TABLES views.
The SYSOBJECTS view is available in SQL Server 2000, 2005, and 2008.
Whereas SYS.TABLES and INFORMATION_SCHEMA.TABLES views are available in SQL Server 2005 and 2008, and later versions only. They are not available in SQL Server 2000.
Now, let’s see how to use the SYSOBJECTS view to retrieve all the tables that are present in the Demo database.
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, functions, and stored procedures.
Select * from [SYSOBJECTS]
So, the SYSOBJECTS view contains all those objects. Now, if I just want the tables, then I filter on the Xtype column.
When I execute the query, notice that I get the tables that are present in the Demo database.
Select * from [SYSOBJECTS] where XTYPE='U'
Now, what does ‘U’ mean in the Xtype column? ‘U’ stands for user table. So where can I find this list? Actually, you can visit the MSDN link for all possible Xtype column values and what they represent.
MSDN-sys.sysobjects (Transact-SQL)
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 can get all the stored procedures. Similarly, if I want just views, then I replace XTYPE with 'V'.
Now, if I want to know what different types of objects are available in the Demo database, I can find out by issuing a distinct query.
Select Distinct XTYPE from [SYSOBJECTS]
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'. Then, you can see all the stored procedures within the Demo database.
Select * from SYS.views Select * from SYS.procedures
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.