SQL Server List Views

Learning Objective

The objective of this SQL Server tutorial is to teach you how to list all the Views in a database.

Why and How to LIST VIEWS?

It might be necessary for a DBA to check the views in a database before creating a view, altering an existing view or to learn about the query used to create a view.

There are 4 ways to do it using 4 different tools. 3 of them are systems views (i.e. views themselves) and 1 of them is a stored procedure. The system views are INFORMATION_SCHEMA.VIEWS, sys.views, and sys.objects. All these views can be seen under Views – > System Views in the SSMS GUI object explorer. The same can be seen in the screenshot below. You can see the INFORMATION_SCHEMA.VIEWS view at the end highlighted in yellow.

SQL Server List Views Examples

We will discuss all the 4 different ways using the 4 different tools one by one.

1) SQL Server List Views using INFORMATION_SCHEMA.VIEWS view

The command to list the views in a database is as below. Before listing the views in a database, we have to use the database. In this example, I have used the master database since my views exist on tables in the master database.

USE master;
SELECT * FROM INFORMATION_SCHEMA.VIEWS;

The output from the above query is below. It lists 3 views. The table_catalog columns lists the database (i.e. master), the table_schema column lists the schema (the default schema dbo), the table_name column the name of the view and the view_defintion column the query for the view.

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE
master	dbo	new_view	CREATE VIEW new_view (id, name, sex, salary) AS select emp_id, emp_name, emp_sex, emp_salary from employees where emp_salary > (select avg(emp_salary) from employees);	NONE	NO
master	dbo	ladies_in_departments	CREATE VIEW ladies_in_departments as select e.emp_id, e.emp_name, e.emp_salary, d.dept_name as emp_department from employees e inner join departments d on e.emp_dept=d.dept_id where e.emp_sex=’Female’;	NONE	NO
master	dbo	spt_values	create view spt_values as  select name collate database_default as name,   number,   type collate database_default as type,   low, high, status  from sys.spt_values	NONE	NO

2) SQL Server List Views using sys.views view

The sys.views view contains 20 columns which produces a substantial output if we do a SELECT * on it. As such it is not very effective. The below command lists the most relevant columns from sys.views for the master database and dbo schema.

SELECT name, type_desc, create_date, modify_date
FROM sys.views;

The output from the above command is the below which lists the 3 views and their creation and modification date.

name	type_desc	create_date	modify_date
new_view	VIEW	10/8/2020 13:25	10/8/2020 13:25
ladies_in_departments	VIEW	10/8/2020 14:11	10/8/2020 14:11
spt_values	VIEW	9/24/2019 14:21	9/24/2019 14:21

From the output above we can see that it does not list the view definition or the view columns. This is because the sys.views view does not contain that information. However, that information is available is another view called sys.sql_modules. The below query retrieves the view definition for each of the views by doing a join between sys.views and sys.sql_modules on object_id column which is common to both views. The object_id as the name implies is an unique id which identifies a database object.

SELECT name, definition
FROM sys.views v
INNER JOIN sys.sql_modules m
ON v.object_id = m.object_id;

The output of the above command is the below. It shows the view query just like it did in the VIEW_DEFINTION column of INFORMATION_SCHEMA.VIEWS.

name	definition
new_view	CREATE VIEW new_view (id, name, sex, salary) AS select emp_id, emp_name, emp_sex, emp_salary from employees where emp_salary > (select avg(emp_salary) from employees);
ladies_in_departments	CREATE VIEW ladies_in_departments as select e.emp_id, e.emp_name, e.emp_salary, d.dept_name as emp_department from employees e inner join departments d on e.emp_dept=d.dept_id where e.emp_sex=’Female’;
spt_values	create view spt_values as  select name collate database_default as name,   number,   type collate database_default as type,   low, high, status  from sys.spt_values

3) SQL Server List Views using sys.objects view

As is obvious from the name the sys.objects view does not just contain information  about views but about all database objects in a particular database. If we do SELECT * on the view for the master db it will return the list of all database objects including system tables, user tables, stored procedures etc in the master db. Therefore, to retrieve relevant information only about views we use the below statement.

SELECT name, type_desc, create_date, modify_date
FROM sys.objects
WHERE type_desc='VIEW';

The output of the above command is the below. It is basically the same output as produced by sys.views above without the join.

name	type_desc	create_date	modify_date
new_view	VIEW	10/8/2020 13:25	10/8/2020 13:25
ladies_in_departments	VIEW	10/8/2020 14:11	10/8/2020 14:11
spt_values	VIEW	9/24/2019 14:21	9/24/2019 14:21

Here also we can join sys.objects with sys.sql_modules as done above for sys.views to get the view definition. The query and output are shown below. The only changes in the query is sys.objects instead of sys.views, alias o instead of v and WHERE clause to filter records of type VIEW only.

SELECT name, definition
FROM sys.objects o
INNER JOIN sys.sql_modules m
ON o.object_id = m.object_id
where type_desc='VIEW';

name	definition
new_view	CREATE VIEW new_view (id, name, sex, salary) AS select emp_id, emp_name, emp_sex, emp_salary from employees where emp_salary > (select avg(emp_salary) from employees);
ladies_in_departments	CREATE VIEW ladies_in_departments as select e.emp_id, e.emp_name, e.emp_salary, d.dept_name as emp_department from employees e inner join departments d on e.emp_dept=d.dept_id where e.emp_sex=’Female’;
spt_values	create view spt_values as  select name collate database_default as name,   number,   type collate database_default as type,   low, high, status  from sys.spt_values

4) SQL Server List Views using sp_tables system stored procedure

The command to list the views using sp_tables is the following. The sp_tables can be used to retrieve information about all tables in the database including system tables and tables owned by system user accounts like sys, INFORMATION_SCHEMA etc. Hence, we have to use @table_owner and @table_type to restrict the output to views owned by the default user dbo. If you have custom users in your SQL Server specify the names of the user account(s) instead of or along with dbo.

EXEC SP_TABLES
@table_owner='dbo',
@table_type="'VIEW'";

The output from the above command is the below.

TABLE_QUALIFIER	TABLE_OWNER	TABLE_NAME	TABLE_TYPE	REMARKS
master	dbo	ladies_in_departments	VIEW	NULL
master	dbo	new_view	VIEW	NULL
master	dbo	spt_values	VIEW	NULL