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)
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.