Sql Query to list all views in an SQL Server 2005 database

SqlSql ServerMetadata

Sql Problem Overview


I need an sql query to enumerate all views (I only need the view names) of a specific database in SQL Server 2005. Thanks in advance!

Sql Solutions


Solution 1 - Sql

To finish the set off (with what has already been suggested):

SELECT * FROM sys.views

This gives extra properties on each view, not available from sys.objects (which contains properties common to all types of object) or INFORMATION_SCHEMA.VIEWS. Though INFORMATION_SCHEMA approach does provide the view definition out-of-the-box.

Solution 2 - Sql

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

Solution 3 - Sql

SELECT  *
FROM    sys.objects
WHERE   type = 'V'

Solution 4 - Sql

Run this adding DatabaseName in where condition.

  SELECT TABLE_NAME, ROW_NUMBER() OVER(ORDER BY TABLE_NAME) AS 'RowNumber' 
  FROM INFORMATION_SCHEMA.VIEWS 
  WHERE TABLE_CATALOG = 'DatabaseName'

or remove where condition adding use.

  use DataBaseName

  SELECT TABLE_NAME, ROW_NUMBER() OVER(ORDER BY TABLE_NAME) AS 'RowNumber' 
  FROM INFORMATION_SCHEMA.VIEWS 

Solution 5 - Sql

select v.name
from INFORMATION_SCHEMA.VIEWS iv
join sys.views v on v.name = iv.Table_Name
where iv.Table_Catalog = 'Your database name'

Solution 6 - Sql

Some time you need to access with schema name,as an example you are using AdventureWorks Database you need to access with schemas.

 SELECT s.name +'.'+v.name FROM sys.views v inner join sys.schemas s on s.schema_id = v.schema_id 

Solution 7 - Sql

This is old, but I thought I'd put this out anyway since I couldn't find a query that would give me ALL the SQL code from EVERY view I had out there. So here it is:

  SELECT SM.definition
  FROM sys.sql_modules SM
  INNER JOIN sys.Objects SO ON SM.Object_id = SO.Object_id
  WHERE SO.type = 'v'

Solution 8 - Sql

Necromancing.

Since you said ALL views, technically, all answers to date are WRONG.
Here is how to get ALL views:

SELECT 
	 sch.name AS view_schema 
	,sysv.name AS view_name 
	,ISNULL(sysm.definition, syssm.definition) AS view_definition 
	,create_date 
	,modify_date 
FROM sys.all_views AS sysv 

INNER JOIN sys.schemas AS sch 
    ON sch.schema_id = sysv.schema_id 
	
LEFT JOIN sys.sql_modules AS sysm 
	ON sysm.object_id = sysv.object_id 

LEFT JOIN sys.system_sql_modules AS syssm 
	ON syssm.object_id = sysv.object_id 

-- INNER JOIN sys.objects AS syso ON syso.object_id = sysv.object_id 
	
WHERE (1=1) 
AND (sysv.type = 'V') -- seems unnecessary, but who knows
-- AND sch.name = 'INFORMATION_SCHEMA' 
/*
AND sysv.is_ms_shipped = 0  
AND NOT EXISTS 
(
    SELECT * FROM sys.extended_properties AS syscrap
    WHERE syscrap.major_id = sysv.object_id
    AND syscrap.minor_id = 0 
    AND syscrap.class = 1 
    AND syscrap.name = N'microsoft_database_tools_support' 
)  
*/

ORDER BY 
	 view_schema 
	,view_name 
	 

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionMatsView Question on Stackoverflow
Solution 1 - SqlAdaTheDevView Answer on Stackoverflow
Solution 2 - SqlmadfragView Answer on Stackoverflow
Solution 3 - SqlDavid MView Answer on Stackoverflow
Solution 4 - SqlhgulyanView Answer on Stackoverflow
Solution 5 - SqlJ SView Answer on Stackoverflow
Solution 6 - SqlElshanView Answer on Stackoverflow
Solution 7 - SqlBrianView Answer on Stackoverflow
Solution 8 - SqlStefan SteigerView Answer on Stackoverflow