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.
