SQL SERVER – List of all the Views from Database

My earlier article SQL SERVER – The Limitations of the Views – Eleven and more… has lots of popularity and I have been asked many questions about the view. Many emails I received suggesting that they have hundreds of the view and now have no clue what is going on and how many of them have indexes and how many does not have an index. Some even asked me if there is any way they can get a list of the views with the property of Index along with it.

Here is the quick script which does exactly the same. You can also include many other columns from the same view.

SELECT SCHEMA_NAME(schema_id) AS schema_name
,name AS view_name
,OBJECTPROPERTYEX(OBJECT_ID,'IsIndexed') AS IsIndexed
,OBJECTPROPERTYEX(OBJECT_ID,'IsIndexable') AS IsIndexable
--,*
FROM sys.views;
GO

You can run above query to see if the view is indexed or created with SCHEAMABINDING.

Do you use Views, if yes, you need to read SQL SERVER – The Limitations of the Views – Eleven and more…

Let me know if you liked the series of the limitation of the view or not. I put lots of the efforts to make it interesting and I would love to see your feedback.

Reference: Pinal Dave (https://blog.sqlauthority.com)