Peter
Peter

Reputation: 14518

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

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?

Upvotes: 87

Views: 183936

Answers (7)

Milica Medic Kiralj
Milica Medic Kiralj

Reputation: 3780

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'  

Upvotes: 22

Lokesh Kumar Gaurav
Lokesh Kumar Gaurav

Reputation: 726

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

exec dbo.dbsearch 'Your_Table_Name'

Upvotes: 0

James Hill
James Hill

Reputation: 61872

This should do it:

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

Upvotes: 154

Dmitry
Dmitry

Reputation: 59

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

Upvotes: 3

Steven de Salas
Steven de Salas

Reputation: 21507

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.

Upvotes: 8

marc_s
marc_s

Reputation: 755531

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

Upvotes: 5

Diego
Diego

Reputation: 36176

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

Upvotes: 3

Related Questions