Thread: Retrieving column names and table names of a database

Retrieving column names and table names of a database

From
"Matthias Seiferth"
Date:
Hi,
, this may sound stupid, but is there a way of retrieving the table names and column names of a database using SQL. I know there is \d in psql, but I don´t know if there is something similar in SQL or if there is a function I can call.
Please reply even if there is no function
 
Thanks in advance for your help
 
Matthias
.

Re: Retrieving column names and table names of a database

From
nourry@ensinfo.univ-nantes.fr (Frederic Nourry)
Date:
>Hi,
>, this may sound stupid, but is there a way of retrieving the table names and >column names of a database using SQL. I
knowthere
 
>is \d in psql, but I don´t know if there is something similar in SQL or if there >is a function I can call. 
>Please reply even if there is no function
>
>Thanks in advance for your help
>
>Matthias
>. 
To retrieve the table names , use the pg_tables :   SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%';
To retrieve the column names , you can use the pg_attribute and
pg_type tables :    SELECT attname , typname FROM pg_attribute , pg_type WHERE    typrelid=attrelid AND typname =
'banals';    if your table name is 'banals' , but this will give you 6 columns that
 
you haven't declared : cmax,xmax,cmin,xmin,oid,ctid .
 If you want column names with its type :     SELECT attname , opcname , atttypmod FROM pg_attribute,pg_type,
pg_opclassWHERE attrelid=typrelid AND atttypid=opcdeftype AND    typname = 'banals' ;         if your table name is
'banals', but this will give the
 
column 'oid' you haven't declared
 You can find all tables names in <where you put psql>//data/base/ .
Sorry for my english !