SQL SERVER – List of all the Views from Database

SQL SERVER - List of all the Views from Database magview 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.

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

Solarwinds
, ,
Previous Post
SQLAuthority News – Blog of Nupur Dave on Windows Live
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

Menu