I have found myself using this simple, but useful SQL time in recent weeks to research different issues and to help with impact analysis. So, I thought I would post it while I’m thinking about it. It just gives a list of views using a table, which can be handy to know. This SQL is simple and could be converted to an equi-join. I used the like statement mostly because I sometimes want to know if there are other views a similar nature in the same family (by naming convention) of tables.
Select All Fields From The _V_View
This is the simplest form of this SQL to views, which a table.
|Select * from _v_view
where DEFINITION like ‘%<<TABLE_NAME>>%’ ;
Select Minimal Fields From The _V_View
This is the version of the SQL, which I normally use, to list the views, which use a table.
|Select VIEWNAME, OWNER from _v_view
where DEFINITION like ‘%<<TABLE_NAME>>%’;