Reputation: 14518
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
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
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
Reputation: 61872
This should do it:
SELECT *
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION like '%YourTableName%'
Upvotes: 154
Reputation: 59
SELECT VIEW_NAME
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE TABLE_NAME = 'Your Table'
Upvotes: 3
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
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).
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