This article presents four ways to return a list of user-defined views in a SQL Server database.

If you want to see only system views, or both user-defined and system views, see Difference Between sys.views, sys.system_views, & sys.all_views in SQL Server.

Option 1 – The VIEWS Information Schema View

You can use the VIEWS information schema view to get a list of all user-defined views in a database.

USE Music;
SELECT 
  TABLE_SCHEMA,
  TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS;

Result:

+----------------+--------------+
| TABLE_SCHEMA   | TABLE_NAME   |
|----------------+--------------|
| dbo            | RockAlbums   |
| dbo            | JazzAlbums   |
| dbo            | BluesAlbums  |
+----------------+--------------+

Return The View’s Definition

The INFORMATION_SCHEMA.VIEWS view also has a VIEW_DEFINITION column, so you can easily return each view’s definition if required.

SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS;

Option 2 – The sys.views System Catalog View

Another way to return a list of views is to query the sys.views system catalog view.

USE Music;
SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  Name
FROM sys.views;

Result:

+----------+-------------+
| Schema   | Name        |
|----------+-------------|
| dbo      | RockAlbums  |
| dbo      | JazzAlbums  |
| dbo      | BluesAlbums |
+----------+-------------+

Return The View’s Definition

The sys.view view doesn’t include a column for the object’s definition. If you want to return each view’s definition, you can join it with the sys.sql_modules system view.

Example:

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

Option 3 – The sys.objects System Catalog View

Another way to return a list of views is to query the sys.objects system catalog view.

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  name
FROM sys.objects
WHERE type = 'V';

Result:

+----------+-------------+
| Schema   | name        |
|----------+-------------|
| dbo      | RockAlbums  |
| dbo      | JazzAlbums  |
| dbo      | BluesAlbums |
+----------+-------------+

The type V is for “View”. Another way to do this is filter by the type_desc column:

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  name
FROM sys.objects
WHERE type_desc = 'VIEW';

Return The View’s Definition

The sys.objects view doesn’t include a column for the object’s definition. If you want to return each view’s definition, you can join it with the sys.sql_modules system view.

Example:

SELECT definition
FROM sys.objects o
INNER JOIN sys.sql_modules m 
ON o.object_id = m.object_id
WHERE type = 'V';

Option 4 – The sp_tables Stored Procedure

The sp_tables stored procedure returns a list of objects that can be queried in the current environment. This includes any table or view, except synonym objects.

You can narrow the results to just views with the @table_type parameter. You can also narrow it down to just a given owner or catalog.

Executing the following code will return all views in the current database – including system views.

EXEC sp_tables @table_type = "'VIEW'";

I won’t present the results here because it’s a long list.

Here it is narrowed down to a given database owner:

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

Result:

+-------------------+---------------+--------------+--------------+-----------+
| TABLE_QUALIFIER   | TABLE_OWNER   | TABLE_NAME   | TABLE_TYPE   | REMARKS   |
|-------------------+---------------+--------------+--------------+-----------|
| Music             | dbo           | BluesAlbums  | VIEW         | NULL      |
| Music             | dbo           | JazzAlbums   | VIEW         | NULL      |
| Music             | dbo           | RockAlbums   | VIEW         | NULL      |
+-------------------+---------------+--------------+--------------+-----------+