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

olhwl3o2  于 2022-12-26  发布在  SQL Server
关注(0)|答案(8)|浏览(195)

我需要一个SQL查询来枚举SQL Server 2005中特定数据库的所有视图(我只需要视图名称)。

6qfn3psc

6qfn3psc1#

结束布景(用已经建议过的):

SELECT * FROM sys.views

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

hl0ma9xz

hl0ma9xz2#

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
wz3gfoph

wz3gfoph3#

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

pdtvr36n4#

在where条件中添加DatabaseName时运行此命令。

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

或移除其中条件添加使用。

use DataBaseName

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

ktecyv1j5#

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'
izj3ouym

izj3ouym6#

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

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

j91ykkif7#

招魂术。
既然你说了所有的观点,从技术上讲,到目前为止所有的答案都是错误的。
下面是如何获取所有视图:

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
plicqrtu

plicqrtu8#

这是旧的,但我想我还是把它放出来,因为我找不到一个查询,它会给予我所有的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'

相关问题