I am trying to search through a group of views to see if they contain certain tables.
For example: let's say I have about 30 views and I want to see if they use this particular table. How can I go about this?
I can not think about how to do this. Anyone have any ideas that can point me in the correct direction?
We are using SQL Server Management Studio v18
CodePudding user response:
SQL Server maintains a list of object references which you can query in a number of ways for the precise table (or any object required), for example:
select o.[Name]
from sys.sql_expression_dependencies d
join sys.objects o on o.object_id=d.referencing_id and o.type='V'
where referenced_id = OBJECT_ID(N'dbo.tablename');
CodePudding user response:
You can select this from INFORMATION_SCHEMA.VIEWS
:
SELECT *
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%Test%';