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
Sql Query to list all views in an SQL Server 2005 database
提问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 schema
name,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'