What are all the views in a database in MS SQL Server?

This is specific to Microsoft SQL Server 2005

  • You can view the same information in a graphical format with the SQL Server Management Studio.
  1. Database
  2. Select the database
  3. Views

Summary

What are all the views in a database with MS SQL server 2005?

Views

use adventureworks;
go

SELECT name AS 'View',SCHEMA_NAME(schema_id) AS 'Schema',
  case 
     when OBJECTPROPERTYEX(object_id,'IsIndexed') = 0 then 'No'
     when OBJECTPROPERTYEX(object_id,'IsIndexed') = 1 then 'Yes'
  end AS 'Indexed',
  case
	 when OBJECTPROPERTYEX(object_id,'IsIndexable') = 0 then 'No'
	 when OBJECTPROPERTYEX(object_id,'IsIndexable') = 1 then 'Yes'
  end AS 'Indexable',
  create_date AS 'Created', modify_date as 'Modified'
FROM sys.views
order by 1;
GO

View                                Schema              Indexed Indexable Created                 Modified
----------------------------------- ------------------- ------- --------- ----------------------- -----------------------
vAdditionalContactInfo              Person              No      No        2005-10-14 01:59:58.460 2005-10-14 01:59:58.460
vEmployee                           HumanResources      No      No        2005-10-14 01:59:58.520 2005-10-14 01:59:58.520
vEmployeeDepartment                 HumanResources      No      No        2005-10-14 01:59:58.630 2005-10-14 01:59:58.630
vEmployeeDepartmentHistory          HumanResources      No      No        2005-10-14 01:59:58.740 2005-10-14 01:59:58.740
vIndividualCustomer                 Sales               No      No        2005-10-14 01:59:58.850 2005-10-14 01:59:58.850
vIndividualDemographics             Sales               No      No        2005-10-14 01:59:58.990 2005-10-14 01:59:58.990
vJobCandidate                       HumanResources      No      No        2005-10-14 01:59:59.117 2005-10-14 01:59:59.117
vJobCandidateEducation              HumanResources      No      No        2005-10-14 01:59:59.333 2005-10-14 01:59:59.333
vJobCandidateEmployment             HumanResources      No      No        2005-10-14 01:59:59.210 2005-10-14 01:59:59.210
vProductAndDescription              Production          Yes     Yes       2005-10-14 01:59:59.397 2005-10-14 01:59:59.537
vProductModelCatalogDescription     Production          No      No        2005-10-14 01:59:59.693 2005-10-14 01:59:59.693
vProductModelInstructions           Production          No      No        2005-10-14 01:59:59.740 2005-10-14 01:59:59.740
vSalesPerson                        Sales               No      No        2005-10-14 01:59:59.833 2005-10-14 01:59:59.833
vSalesPersonSalesByFiscalYears      Sales               No      No        2005-10-14 01:59:59.943 2005-10-14 01:59:59.943
vStateProvinceCountryRegion         Person              Yes     Yes       2005-10-14 02:00:00.053 2005-10-14 02:00:00.163
vStoreWithDemographics              Sales               No      No        2005-10-14 02:00:00.303 2005-10-14 02:00:00.303
vVendor                             Purchasing          No      No        2005-10-14 02:00:00.380 2005-10-14 02:00:00.380

(17 row(s) affected)
  • Do not forget to first select the database that you want to use.