0

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

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
mrmcg
  • 173
  • 6
  • 17
  • You could check if the table name(s) appear in the `VIEW`'s [definition in the `sys.sql_modules`](https://stackoverflow.com/questions/4765323/is-there-a-way-to-retrieve-the-view-definition-from-a-sql-server-using-plain-ado) or [check the table's dependencies](https://stackoverflow.com/questions/22005698/how-to-find-all-the-dependencies-of-a-table-in-sql-server). – Thom A Dec 13 '21 at 12:20
  • Using SSMS, right clic on your table, then "View dependencies", you can see all the view, stored procedure depending on the talbe. – Dordi Dec 13 '21 at 12:21

2 Answers2

2

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');
Stu
  • 30,392
  • 6
  • 14
  • 33
0

You can select this from INFORMATION_SCHEMA.VIEWS:

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

MS SQL view

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
  • 1
    Doesn't really help you if you have a view `SELECT * FROM OtherTableCalledTest` or `SELECT * FROM OtherTable WHERE SomeCol = 'Test'`. You need `sys.sql_expression_dependencies` – Charlieface Dec 13 '21 at 13:03