Sql Query 列出 SQL Server 2005 数据库中的所有视图

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/2903262/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 06:20:36  来源:igfitidea点击:

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

sqlsql-servermetadata

提问by Mats

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 查询来枚举 SQL Server 2005 中特定数据库的所有视图(我只需要视图名称)。提前致谢!

回答by AdaTheDev

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.

这为每个视图提供了额外的属性,而 sys.objects(包含所有类型的对象共有的属性)或 INFORMATION_SCHEMA.VIEWS 中不可用。尽管 INFORMATION_SCHEMA 方法确实提供了开箱即用的视图定义。

回答by madfrag

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

回答by David M

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

回答by hgulyan

Run this adding DatabaseName in where condition.

在 where 条件下运行此添加 DatabaseName。

  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.

或删除 where 条件添加使用。

  use DataBaseName

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

回答by J S

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'

回答by Elshan

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

有时您需要使用schema名称访问,例如您使用 AdventureWorks 数据库,您需要使用架构访问。

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

回答by Stefan Steiger

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 

回答by Brian

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:

这是旧的,但我想无论如何我都会把它拿出来,因为我找不到一个查询,可以从我有的每个视图中给我所有的 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'