如何了解 SQL Server 中视图使用的表?是否有脚本或工具可以让我知道视图中使用的表,并列出字段?希望这清楚了问题,如果有疑问请告诉我。请指导!谢谢!
select
cols.*
from
sys.sql_expression_dependencies objs
outer apply sys.dm_sql_referenced_entities ( OBJECT_SCHEMA_NAME(objs.referencing_id) + N'.' + object_name(objs.referencing_id), N'OBJECT' ) as cols
where
objs.referencing_id = object_id('view_name_here')
referenced_entity_name
中,列名在referenced_minor_name
中。如果对你不起作用,请尝试提供一个更合格的视图名称(例如dbo.view_name
)或重新编译视图再进行检查。 - GSergselect referenced_entity_name,referenced_minor_name from sys.dm_sql_referenced_entities ( 'dbo.foo', N'OBJECT' ) where referenced_minor_name is not null
? OUTER APPLY
看起来只是重复调用相同的值并带来额外的行。 - Martin Smith查看(大多数)对象内容的最简单方法是:
sp_helptext blah
在这里,您可以用对象的名称替换 blah。这将产生创建该对象的实际代码。例如,在这种情况下,它可能会导致:
CREATE VIEW blah
AS
select blah.column1,blah.column2 from blah_table
SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE View_Name = 'ViewName'
这些信息可以从INFORMATION_SCHEMA获取
SELECT *
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS cu
JOIN INFORMATION_SCHEMA.COLUMNS AS c
ON c.TABLE_SCHEMA = cu.TABLE_SCHEMA
AND c.TABLE_CATALOG = cu.TABLE_CATALOG
AND c.TABLE_NAME = cu.TABLE_NAME
AND c.COLUMN_NAME = cu.COLUMN_NAME
WHERE cu.VIEW_NAME = 'viewtablename';
Select DISTINCT cols.referenced_entity_name from
sys.sql_expression_dependencies objs outer apply
sys.dm_sql_referenced_entities (
OBJECT_SCHEMA_NAME(objs.referencing_id) + N'.' +
object_name(objs.referencing_id), N'OBJECT' ) as cols where
objs.referencing_id = object_id('viewname')