SQL Server List Views

Summary: in this tutorial, you will learn how to list all views in the SQL Server database by querying the system catalog view.

To list all views in a SQL Server Database, you query the sys.views or sys.objects catalog view. Here is an example:

SELECT 
	OBJECT_SCHEMA_NAME(v.object_id) schema_name,
	v.name
FROM 
	sys.views as v;Code language: SQL (Structured Query Language) (sql)

The query returns the following list of schema names and view names:

In this example, we used the OBJECT_SCHEMA_NAME() function to get the schema names of the views.

The following query returns a list of views through the sys.objects view:

SELECT 
	OBJECT_SCHEMA_NAME(o.object_id) schema_name,
	o.name
FROM
	sys.objects as o
WHERE
	o.type = 'V';
Code language: SQL (Structured Query Language) (sql)

Creating a stored procedure to show views in SQL Server Database

The following stored procedure wraps the query above to list all views in the SQL Server Database based on the input schema name and view name:

CREATE PROC usp_list_views(
	@schema_name AS VARCHAR(MAX)  = NULL,
	@view_name AS VARCHAR(MAX) = NULL
)
AS
SELECT 
	OBJECT_SCHEMA_NAME(v.object_id) schema_name,
	v.name view_name
FROM 
	sys.views as v
WHERE 
	(@schema_name IS NULL OR 
	OBJECT_SCHEMA_NAME(v.object_id) LIKE '%' + @schema_name + '%') AND
	(@view_name IS NULL OR
	v.name LIKE '%' + @view_name + '%');Code language: SQL (Structured Query Language) (sql)

For example, if you want to know the views that contain the word sales, you can call the stored procedure usp_list_views:

EXEC usp_list_views @view_name = 'sales'Code language: SQL (Structured Query Language) (sql)

Here is the result:

In this tutorial, you have learned various ways to list views in a SQL Server Database by querying data from the system catalog views.