我需要一条 SQL 查询语句,枚举出指定数据库中所有视图的名称(仅需要视图名称),该数据库是 SQL Server 2005。
我需要一条 SQL 查询语句,枚举出指定数据库中所有视图的名称(仅需要视图名称),该数据库是 SQL Server 2005。
为了结束这个(与已经建议的)设置:
SELECT * FROM sys.views
这提供了每个视图的额外属性,这些属性在sys.objects中不可用(该对象包含所有类型对象的通用属性)或INFORMATION_SCHEMA.VIEWS中不可用。虽然INFORMATION_SCHEMA方法可以提供视图定义的开箱即用。
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 *
FROM sys.objects
WHERE type = 'V'
sys.views
。 - marc_sschema
,比如您正在使用AdventureWorks数据库,则需要使用模式进行访问。 SELECT s.name +'.'+v.name FROM sys.views v inner join sys.schemas s on s.schema_id = v.schema_id
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
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'
死灵术。
既然你说“所有视图”,从技术上讲,到目前为止所有的答案都是错误的。
这里是获取所有视图的方法:
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
虽然这很老旧,但我还是想把它分享出来,因为我找不到一个查询可以给我所有视图中的所有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'