How to tell if a table is referenced by a view?

[beth]

Hi ,

I need to modify some tables (i.e. add columns). Is there as easy way that I can check if there are any objects (i.e. views) that reference a particular table?

[JimKnicely]

Beth,

One way is to search the view definition for the table name...

Example

Code: Select all

dbadmin=> create table snoopy (c1 varchar(1));
CREATE TABLE
dbadmin=> create view woodstock as select * from snoopy;
CREATE VIEW
dbadmin=> select table_schema, table_name, view_definition from views where view_definition ilike '%snoopy%';
 table_schema | table_name |            view_definition
--------------+------------+---------------------------------------
 snowfall     | woodstock  | SELECT snoopy.c1 FROM snowfall.snoopy
(1 row)