SQL SERVER – List of all the Views from Database

SQL Server views can be easy to lose track of in a database with many objects. Readers asked me how to list those views and see which ones have an index. Here is a query you can run in the database you want to inspect.

Gouache painting of a magnifying glass examining database views

List Views and Index Information

The query reads sys.views and checks sys.indexes for a clustered index. It also shows whether the view is schema-bound and what SQL Server reports for its indexable property.

SELECT SCHEMA_NAME(v.schema_id) AS schema_name,
       v.name AS view_name,
       CAST(OBJECTPROPERTYEX(v.object_id, 'IsSchemaBound') AS int) AS is_schema_bound,
       CAST(OBJECTPROPERTYEX(v.object_id, 'IsIndexable') AS int) AS is_indexable,
       CASE WHEN EXISTS
       (
           SELECT 1 FROM sys.indexes AS i
           WHERE i.object_id = v.object_id AND i.index_id = 1
       ) THEN 1 ELSE 0 END AS has_clustered_index
FROM sys.views AS v
ORDER BY schema_name, view_name;

Run the query in the target database. The schema name helps distinguish views that share a name in different schemas. The result only includes metadata the current user can see.

Read the Results Carefully

A value of 1 in has_clustered_index means the view has a clustered index. An indexed view requires a unique clustered index before other indexes can be created on it.

Schema binding is one requirement for an indexed view, but it is not the only requirement. The indexable property and the presence of an index answer different questions. A view can be indexable without already having a clustered index.

OBJECTPROPERTYEX can return NULL when the object is not visible or the property cannot be evaluated. If the result differs from what you expect, check the database context and permissions before drawing a conclusion.

What to Do Next

This inventory is a starting point, not a recommendation to create indexes on every view. Indexes add storage and maintenance costs. Evaluate the workload and review the requirements for indexed views before making changes.

For more on the topic, see my earlier post on the limitations of views.

A list of views is not just an inventory, it is a starting point for understanding your database.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.


Discover more from SQL Authority with Pinal Dave

Subscribe to get the latest posts sent to your email.

SQL Scripts, SQL Server, SQL View
Previous Post
Resumable ADD CONSTRAINT in SQL Server 2022: Pausing a Primary Key Build
Next Post
SQL SERVER – Minimum Maximum Memory – Server Memory Options

Related Posts

8 Comments. Leave new

  • Hi sir,
    NO doubt sir, This is really wonderful script.
    but i have one doubt.
    Is there any possibility to know the object for each view belongs in a database ?
    I think u got my doubt.
    Thanks.

    Reply
    • Do you want to know the source database in which view is created?

      Reply
    • I’ve been looking for approximately the same answer for nearly a year:
      Today I found it, so here you have it –

      select vws.name, vws.object_id, tbl.name, tbl.object_id, col.name, col.column_ID
      from sys.views as vws
      join sys.sysdepends as dep
      on dep.id = vws.object_id
      join sys.tables as tbl
      on dep.depid = tbl.object_id
      join sys.columns as col
      on dep.depid = col.object_id
      and dep.depNumber = col.column_ID
      where dep.ID = ”

      You can of course expand the information in this query if you are familiar with the tables used.

      Reply
  • Hello Sir,

    I donot understand in the book what is difference between stord procedure and function,

    So plz guide me in simple languagde with more then three example

    Thanks

    Reply
  • hi pinal/all,

    can u pls tell me how views are shorter then normal programs.explain with an sample example.

    waiting for ur reply…

    Reply
  • This is very helpful. I would love to know how to modify it to include the fields [and ideally their source tables] as well!

    Reply
  • Hello Sir,
    as part of my testing i need to find if each index in Database1 has a corresponding view in Database2 (some of the index names and view names are different).
    I also need to find column order in the index is same as the colum order in view and column names should match.
    Please provide a query.
    thanks.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.