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.

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.





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.
Do you want to know the source database in which view is created?
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.
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
hi pinal/all,
can u pls tell me how views are shorter then normal programs.explain with an sample example.
waiting for ur reply…
Please read about Views in SQL Server help file. If you have any specific doubt ask us.
This is very helpful. I would love to know how to modify it to include the fields [and ideally their source tables] as well!
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.