-1

I am using the postgresql db for openproject. I added two custom column in front end webpage ie. Actual start date and Actual End date . I need to record the start and end of the actual dates for a project. I dont know which table both columns has been created and storing the records. My database is having 110 tables and its really hard for me to search each table one by one. Can you please help and give me a query to find the both columns. Thanks in advance.

vivek rajagopalan
  • 843
  • 3
  • 13
  • 22

1 Answers1

0

This gives you the schema name as well as the table name of a specific column.

select t.table_schema,
       t.table_name,
       c.column_name 
from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name 
                                and c.table_schema = t.table_schema
where c.column_name = 'column_name'
      and t.table_schema not in ('information_schema', 'pg_catalog')
      and t.table_type = 'BASE TABLE'
order by t.table_schema;

Where obviously 'column_name' is the name you have to pass in for a column.

phaen
  • 349
  • 2
  • 12
  • select t.table_schema, t.table_name from information_schema.tables t inner join information_schema.columns c on c.table_name = t.table_name and c.table_schema = t.table_schema where c.column_name = '%actual%start%date%' and t.table_schema not in ('information_schema', 'pg_catalog') and t.table_type = 'BASE TABLE' order by t.table_schema; I tried to get the column name but i am not getting any results. – vivek rajagopalan Feb 12 '20 at 10:35
  • I updated my post and also select the column_name, to show you the exact name of the column find. What you are now having trouble with is SQL standard. Using placeholders like % in a string clause forces you to not use equals (=) but the 'like' clause. You have to go with "[...] where c.column_name like '%actual%start%date%' [...]" – phaen Feb 12 '20 at 10:43