There are times when you want to find all of the views that are in your Microsoft SQL database. Especially when there are 1,000’s of views. Each with the naming convention of the previous 5 DBA’s personal whims. Normally as a database ages the views will become duplicative and add to the general clutter that gets in the way of efficiency.
List all views in a database
Below is the query that will list all of the view in the database.
|
1 2 3 4 5 6 7 8 9 |
-- --------------------------------------------------------------------------- -- List all views for a database -- --------------------------------------------------------------------------- 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; |
Explanation
The query will run against the system table sys.views. The query returns the schema name, view name and if the view has columns that are indexed or indexable.
Caveat
You will need elevated privileges to system access to run a query against the system tables. You only need elevated privileges for the database you want to query.
Leave a Reply