CODEWITHSUNDEEP
sqlsql-serverinformation-schema
mrmcg
mrmcg

Reputation: 193

Search a group of SQL Server views for tables use

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

Upvotes: 0

Views: 224

Answers (2)

Stu
Stu

Reputation: 32599

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');

Upvotes: 2

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

You can select this from INFORMATION_SCHEMA.VIEWS:

SELECT * 
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%Test%';

MS SQL view

Upvotes: 0

Related Questions