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 on 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 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 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…

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

8 thoughts on “SQL SERVER – List of all the Views from Database

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

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s