How can I identify the tables a Microsoft SQL Server view is using?

gpnt7bae 于 2023-06-04 发布在 SQL Server

关注(0)|答案(2)|浏览(754)

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.

sql-server

来源：https://stackoverflow.com/questions/76353345/how-can-i-identify-the-tables-a-microsoft-sql-server-view-is-using

2条答案

按热度按时间

pes8fvy91#

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

赞(0）回复(0）举报 2023-06-04

9q78igpj2#

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.

赞(0）回复(0）举报 2023-06-04

我来回答

How can I identify the tables a Microsoft SQL Server view is using?

2条答案

相关问题

热门标签

最新问答