英文:
How can I identify the tables a Microsoft SQL Server view is using?
问题
我在Microsoft SQL Server中工作，被要求查看某个视图的数据来自哪些表格。我尝试使用依赖关系选项卡，但只看到视图的名称。这是什么意思？我已经确认这确实是一个视图。
我尝试使用以下查询：
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('NameOfView');
我还尝试了以下查询：
EXEC sp_helptext 'NameofView';
最后，我尝试了以下查询：
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
WHERE VIEW_NAME = 'NameofView';
这些查询都没有返回任何结果。
英文:
I'm working in Microsoft SQL Server and was asked what tables does a certain view gets its data. I tried using the dependencies tab but all I see is the name of the view. What does this mean? I have verified that it is in fact a view.
I tried using
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('NameOfView');
I also tried
EXEC sp_helptext 'NameofView';
Lastly,
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
WHERE VIEW_NAME = 'NameofView';
None of these returned anything.
答案1
得分: 2
你可以使用 sys.dm_sql_referenced_entities。
SELECT
referenced_server_name,
referenced_database_name,
referenced_schema_name,
referenced_entity_name
FROM sys.dm_sql_referenced_entities('dbo.NameOfView', 'OBJECT') r
WHERE r.referenced_minor_name IS NULL; -- 忽略列引用
英文:
You can use sys.dm_sql_referenced_entities
SELECT
referenced_server_name,
referenced_database_name,
referenced_schema_name,
referenced_entity_name
FROM sys.dm_sql_referenced_entities('dbo.NameOfView', 'OBJECT') r
WHERE r.referenced_minor_name IS NULL; -- ignore column references
答案2
得分: 0
你问道：
> 一个特定视图获取其数据的表是哪些
事实上，99%的人会在SSMS对象资源管理器中编写视图脚本，并查看视图执行的查询。 查询包含了所使用的表。
在SSMS对象资源管理器中右键单击视图 > 脚本视图为 > 创建到 > 新查询窗口。 使用的表将在视图脚本中显示。
我想不出除了面试问题以测试您对SSMS知识的了解或者如果您的同事懒得自己编写脚本的情况下，还有什么好的理由提出这个问题。
英文:
You asked:
> what tables does a certain view gets its data
The reality is 99% of people script the view in the SSMS Object Explorer and look at the query executed by the view. The query contains the tables used.
Right-click the view in SSMS Object Explorer > Script View As > Create To > New Query Window. The tables used will be displayed in the view script.
I can't think of any good reason to ask this question outside of an interview question to test your knowledge of SSMS or if your co-worker was too lazy to script it out themselves.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论