How to find which views are using a certain table in SQL Server (2008)?

Question

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?

Answer 1

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

SELECT DISTINCT
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'  

Answer 2

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

exec dbo.dbsearch 'Your_Table_Name'

Answer 3

This should do it:

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

Answer 4

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

Answer 5

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.

Answer 6

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).

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??

Answer 7

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