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
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
8条答案
按热度按时间6qfn3psc1#
结束布景(用已经建议过的):
这为每个视图提供了额外的属性,这些属性在sys.objects(包含所有类型的对象共有的属性)或INFORMATION_SCHEMA. VIEWS中不可用。尽管INFORMATION_SCHEMA方法确实提供了现成的视图定义。
hl0ma9xz2#
wz3gfoph3#
pdtvr36n4#
在where条件中添加DatabaseName时运行此命令。
或移除其中条件添加使用。
ktecyv1j5#
izj3ouym6#
有时您需要使用
schema
名称进行访问,例如,您使用的AdventureWorks数据库需要使用架构进行访问。j91ykkif7#
招魂术。
既然你说了所有的观点,从技术上讲,到目前为止所有的答案都是错误的。
下面是如何获取所有视图:
plicqrtu8#
这是旧的,但我想我还是把它放出来,因为我找不到一个查询,它会给予我所有的SQL代码从每个视图我有在那里。