Date: Fri, 21 Apr 2006 14:31:41 -0400
From: George Young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: Re: find all tables with a specific column name?
Message-Id: <20060421143141.1f3681fb.gry@ll.mit.edu>
In-Reply-To: <Pine.LNX.4.64.0604210928220.1527@glacier.frostconsultingllc.com>
References: <Pine.LNX.4.64.0604210928220.1527@glacier.frostconsultingllc.com>
Organization: MIT Lincoln Laboratory
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

I've found it useful to explore the information_schema schema by doing:

  set search_path=information_schema;  -- Lets just look at the system tables.
  \d                                   -- Show me all the tables.
...
(40 rows)

then 
  select * from some-likely-looking-table limit 20;

In this case, I quickly found a table called "columns", so you can do:
  select table_name from information_schema.columns where 
  column_name='last_modified';

Of course you could be a wuss and actually read the documentation ;-)
  http://www.postgresql.org/docs/8.1/interactive/infoschema-columns.html

-- George Young

On Fri, 21 Apr 2006 09:29:33 -0700 (PDT)
Jeff Frost <jeff@frostconsultingllc.com> wrote:

> Is there a reasonable way to extract a list of all tables which contain a 
> specific column name from the system views on 8.1?
> 
> For instance, I might want to enumerate all tables with a column named 
> last_modified.
> 
> -- 
> Jeff Frost, Owner 	<jeff@frostconsultingllc.com>
> Frost Consulting, LLC 	http://www.frostconsultingllc.com/
> Phone: 650-780-7908	FAX: 650-649-1954
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
> 


-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)