Listing Tables, Views, and Procedures in SQL Server

In SQL Server, you can use system views and catalog views to list all the tables, views, and procedures in a database.

Listing Tables

To list all the tables in a database, you can use the `sys.tables` system view:

SELECT name FROM sys.tables WHERE type = 'U'; 
-- 'U' for user-defined tables

This query returns a list of all the user-defined tables in the current database.

Result

name
Customers
Orders
Products

Listing Views

To list all the views in a database, you can use the `sys.views` system view:

SELECT name FROM sys.views;

This query returns a list of all the views in the current database.

Result

name
v_CustomerOrders
v_ProductSales

Listing Procedures

To list all the procedures in a database, you can use the `sys.procedures` system view:

SELECT name FROM sys.procedures;

This query returns a list of all the procedures in the current database.

Result

name
usp_GetCustomerOrders
usp_UpdateProductPrice

Listing All Objects

If you want to list all the tables, views, and procedures in a database, you can use the `sys.objects` system view:

SELECT name, type_desc FROM sys.objects WHERE type IN ('U', 'V', 'P'); 
-- 'U' for user-defined tables, 'V' for views, 'P' for procedures

This query returns a list of all the tables, views, and procedures in the current database.

Result

name type_desc
Customers USER_TABLE
Orders USER_TABLE
v_CustomerOrders VIEW
usp_GetCustomerOrders SQL_STORED_PROCEDURE