如何查找哪些视图正在使用 SQL Server (2008) 中的某个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9618134/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find which views are using a certain table in SQL Server (2008)?
提问by Peter
I have to add a few columns to a table and I also need to add these columns to all the views that use this table.
我必须向表中添加几列，并且还需要将这些列添加到使用该表的所有视图中。
Is it possible to get a list of all the views in a database that use a certain table?
是否可以获取数据库中使用某个表的所有视图的列表？
回答by James Hill
This should do it:
这应该这样做：
SELECT *
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION like '%YourTableName%'
回答by Milica Medic
To find table dependencies you can use the sys.sql_expression_dependencies catalog view:
要查找表依赖项，您可以使用 sys.sql_expression_dependencies 目录视图：
SELECT
referencing_object_name = o.name,
referencing_object_type_desc = o.type_desc,
referenced_object_name = referenced_entity_name,
referenced_object_type_desc =so1.type_desc
FROM sys.sql_expression_dependencies sed
INNER JOIN sys.views o ON sed.referencing_id = o.object_id
LEFT OUTER JOIN sys.views so1 ON sed.referenced_id =so1.object_id
WHERE referenced_entity_name = 'Person'
You can also try out ApexSQL Searcha free SSMS and VS add-in that also has the View Dependencies feature. The View Dependencies feature has the ability to visualize all SQL database objects' relationships, including those between encrypted and system objects, SQL server 2012 specific objects, and objects stored in databases encrypted with Transparent Data Encryption (TDE)
您还可以试用ApexSQL Search一个免费的 SSMS 和 VS 加载项，它还具有查看依赖项功能。查看依赖项功能能够可视化所有 SQL 数据库对象的关系，包括加密对象和系统对象之间的关系、SQL Server 2012 特定对象以及存储在使用透明数据加密 (TDE) 加密的数据库中的对象
Disclaimer: I work for ApexSQL as a Support Engineer
免责声明：我作为支持工程师为 ApexSQL 工作
回答by marc_s
If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREERed-Gate tool called SQL Searchwhich does this - it searches your entire database for any kind of string(s).
如果您需要按名称查找数据库对象（例如表、列、触发器） - 请查看名为SQL Search的免费Red-Gate 工具，它可以执行此操作 - 它会在您的整个数据库中搜索任何类型的字符串。
It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREEto use for any kind of use??
对于任何 DBA 或数据库开发人员来说，它都是一个很棒的必备工具 - 我是否已经提到它对于任何类型的使用都是完全免费的？？
回答by Steven de Salas
I find this works better:
我发现这更有效：
SELECT type, *
FROM sys.objects
WHERE OBJECT_DEFINITION(object_id) LIKE '%' + @ObjectName + '%'
AND type IN ('V')
ORDER BY name
Filtering VIEW_DEFINTIONinside INFORMATION_SCHEMA.VIEWSis giving me quite a few false positives.
VIEW_DEFINTION内部过滤INFORMATION_SCHEMA.VIEWS给了我很多误报。
回答by Diego
select your table -> view dependencies -> Objects that depend on
选择您的表 -> 查看依赖项 -> 依赖的对象
回答by Dmitry
SELECT VIEW_NAME
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE TABLE_NAME = 'Your Table'
回答by Lokesh Kumar Gaurav
Simplest way to find used view or stored procedure for the tableName using below query -
使用以下查询查找 tableName 的已用视图或存储过程的最简单方法 -
exec dbo.dbsearch 'Your_Table_Name'
