如何查找在 SQLServer (2008)中使用某个表的视图？

我必须向一个表中添加一些列，还需要将这些列添加到使用该表的所有视图中。

是否有可能获得数据库中使用某个表的所有视图的列表？

142921 次浏览
小开
最佳答案

This should do it:

SELECT *
FROM   INFORMATION_SCHEMA.VIEWS
WHERE  VIEW_DEFINITION like '%YourTableName%'
小开

select your table -> view dependencies -> Objects that depend on

小开

If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

enter image description here

enter image description here

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??

小开

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_DEFINTION inside INFORMATION_SCHEMA.VIEWS is giving me quite a few false positives.

小开

To find table dependencies you can use the sys.sql_expression_dependencies catalog view:

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 Search a 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)

Disclaimer: I work for ApexSQL as a Support Engineer

小开 
SELECT VIEW_NAME
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE TABLE_NAME = 'Your Table'
小开

Simplest way to find used view or stored procedure for the tableName using below query -

exec dbo.dbsearch 'Your_Table_Name'

提交答案