• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
sqlserver tutorial

SQL Server Tutorial

SQL Server Tutorial for Beginners

  • HOME
  • START HERE
  • BASICS
  • ADVANCED
    • Index
    • Views
    • Triggers
    • Stored Procedures
    • User-defined Functions
  • FUNCTIONS
Home / SQL Server Views / SQL Server List Views

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

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_CATALOGTABLE_SCHEMATABLE_NAMEVIEW_DEFINITIONCHECK_OPTIONIS_UPDATABLE
masterdbonew_viewCREATE 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);NONENO
masterdboladies_in_departmentsCREATE 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’;NONENO
masterdbospt_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 NONENO

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.

nametype_desccreate_datemodify_date
new_viewVIEW10/8/2020 13:2510/8/2020 13:25
ladies_in_departmentsVIEW10/8/2020 14:1110/8/2020 14:11
spt_valuesVIEW9/24/2019 14:219/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.

namedefinition
new_viewCREATE 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_departmentsCREATE 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.

nametype_desccreate_datemodify_date
new_viewVIEW10/8/2020 13:2510/8/2020 13:25
ladies_in_departmentsVIEW10/8/2020 14:1110/8/2020 14:11
spt_valuesVIEW9/24/2019 14:219/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';
namedefinition
new_viewCREATE 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_departmentsCREATE 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_QUALIFIERTABLE_OWNERTABLE_NAMETABLE_TYPEREMARKS
masterdboladies_in_departmentsVIEWNULL
masterdbonew_viewVIEWNULL
masterdbospt_valuesVIEWNULL
Was this tutorial helpful?
YesNo
« Previous: SQL Server Create View
SQL Server Rename View :Next »

Reader Interactions

Trackbacks

  1. Where Can I Find Views In Sql Server? – TLDR TEch says:
    January 27, 2022 at 12:05 pm

    […] SQL Server List Views […]

    Reply

Primary Sidebar

DATA MANIPULATION

  • SELECT
  • SELECT TOP
  • SELECT DISTINCT
  • OFFSET FETCH
  • ORDER BY
  • GROUP BY
  • BETWEEN
  • LIKE
  • ALIAS
  • HAVING
  • AND
  • OR
  • IN
  • WHERE
  • SELECT INTO
  • INSERT
  • INSERT Multiple rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE CONSTRAINT
  • NOT NULL CONSTRAINT
  • SUBQUERY
  • CORRELATED SUBQUERY
  • JOINS
  • CROSS JOIN
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • SELF JOIN
  • UPDATE Join
  • CASE
  • COALESCE
  • NULL
  • NULLIF
  • UNION
  • INTERSECT
  • MERGE
  • EXCEPT
  • EXISTS
  • GROUPING SET
  • PIVOT
  • ROLLUP
  • CUBE

DATA DEFINITION

  • CREATE DATABASE
  • DROP DATABASE
  • CREATE SCHEMA
  • ALTER SCHEMA
  • DROP SCHEMA
  • CREATE TABLE
  • RENAME TABLE
  • DROP TABLE
  • TRUNCATE TABLE
  • IDENTITY column
  • Sequence
  • ALTER TABLE ADD Column
  • ALTER TABLE ALTER Column
  • ALTER TABLE DROP Column

Footer

About

SQLServerTutorial.org provides free tutorials and guide on SQL Server for Developers, Database Administrators, and Solution Architects who want to get started SQL Server quickly.

Recent Posts

  • SQL Server DROP VIEW
  • SQL Server Indexed View
  • Check view definition
  • SQL Server Rename View
  • SQL Server List Views

Quick Links

  • About
  • Contact Us
  • Privacy Policy
  • SQL Server Index
  • SQL Server Views
  • Terms of Use

Copyright © 2023 www.sqlservertutorial.org. All Rights Reserved.