列出SQL Server 2005数据库中所有视图的SQL查询

Question

我需要一条 SQL 查询语句，枚举出指定数据库中所有视图的名称（仅需要视图名称），该数据库是 SQL Server 2005。

- Mats

Answer 1

为了结束这个（与已经建议的）设置：

SELECT * FROM sys.views

这提供了每个视图的额外属性，这些属性在sys.objects中不可用（该对象包含所有类型对象的通用属性）或INFORMATION_SCHEMA.VIEWS中不可用。虽然INFORMATION_SCHEMA方法可以提供视图定义的开箱即用。

- AdaTheDev

---

错误 - 无效的对象名称 'sys.views'。我正在查询主数据库。 - Steam

Answer 2

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

- madfrag

---

SQL Server中数据库中所有视图的列表 - MikeTeeVee

Answer 3

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

- David M

---

尽量避免使用通用的“sys.objects” - 而是使用更具体的视图 - sys.views。 - marc_s

@marc_s，请问您能解释一下为什么会这样吗？ - Matt Shepherd

Answer 4

有时候您需要访问带有模式名的schema，比如您正在使用AdventureWorks数据库，则需要使用模式进行访问。

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

- Elshan

---

远比标准的“sys.views”更有帮助。并不是每个人都会在DBO模式中删除表格。 - Damian

Answer 5

在where条件中添加DatabaseName并运行。

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

或者移除where条件并添加use。

  use DataBaseName

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

- hgulyan

Answer 6

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'

- J S

Answer 7

死灵术。

既然你说“所有视图”，从技术上讲，到目前为止所有的答案都是错误的。
这里是获取所有视图的方法：

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 

- Stefan Steiger

Answer 8

虽然这很老旧，但我还是想把它分享出来，因为我找不到一个查询可以给我所有视图中的所有SQL代码。所以在这里：

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

- Brian