Thread: SQL to get a table columns comments?
Hi, What query can I run to get the comments for my table columns. i.e. the ones on my 8.1 database added with this command: COMMENT ON COLUMN addresses.address_id IS 'Unique identifier for the addresses table'; thanks Tim
am Sat, dem 27.01.2007, um 17:57:06 -0800 mailte Timasmith folgendes: > Hi, > > What query can I run to get the comments for my table columns. > > i.e. the ones on my 8.1 database added with this command: > > COMMENT ON COLUMN addresses.address_id IS 'Unique identifier for the > addresses table'; Start psql with Option -E and type '\d+ <tablename>'. (without the ') Then you can see the underlaying SQL-Query to request this. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
SELECT
CASE
WHEN sfl.description IS NOT NULL THEN sfl.description
WHEN sfl.description IS NULL THEN pa.attname::character varying
ELSE pd.description::character varying
END AS label
FROM ONLY pg_class pc
JOIN ONLY pg_attribute pa ON pc.oid = pa.attrelid AND pc.relnamespace =
2200::oid AND pc.reltype > 0::oid AND (pc.relkind = 'r'::"char" OR
pc.relkind = 'c'::"char" OR pc.relkind = 'v'::"char")
JOIN ONLY pg_type pt ON pa.atttypid = pt.oid
LEFT JOIN ONLY pg_description pd ON pc.oid = pd.objoid AND pa.attnum =
pd.objsubid
LEFT JOIN sys_flex_labels sfl ON pc.oid = sfl.table_oid::oid AND
pa.attname::character varying::text = sfl.column_name::text
WHERE pa.attnum > 0
ORDER BY pc.relname::character varying, pa.attnum;
"Timasmith" <timasmith@hotmail.com> wrote in message
news:1169949426.721522.117610@a75g2000cwd.googlegroups.com...
> Hi,
>
> What query can I run to get the comments for my table columns.
>
> i.e. the ones on my 8.1 database added with this command:
>
> COMMENT ON COLUMN addresses.address_id IS 'Unique identifier for the
> addresses table';
>
> thanks
>
> Tim
>
On Jan 30, 12:15 pm, "codeWarrior" <gpatn...@hotmail.com> wrote: > SELECT > CASE > WHEN sfl.description IS NOT NULL THEN sfl.description > WHEN sfl.description IS NULL THEN pa.attname::character varying > ELSE pd.description::character varying > END AS label > FROM ONLY pg_class pc > JOIN ONLY pg_attribute pa ON pc.oid = pa.attrelid AND pc.relnamespace = > 2200::oid AND pc.reltype > 0::oid AND (pc.relkind = 'r'::"char" OR > pc.relkind = 'c'::"char" OR pc.relkind = 'v'::"char") > JOIN ONLY pg_type pt ON pa.atttypid = pt.oid > LEFT JOIN ONLY pg_description pd ON pc.oid = pd.objoid AND pa.attnum = > pd.objsubid > LEFT JOIN sys_flex_labels sfl ON pc.oid = sfl.table_oid::oid AND > pa.attname::character varying::text = sfl.column_name::text > WHERE pa.attnum > 0 > ORDER BY pc.relname::character varying, pa.attnum; > > "Timasmith" <timasm...@hotmail.com> wrote in message > > news:1169949426.721522.117610@a75g2000cwd.googlegroups.com... > > > > > Hi, > > > What query can I run to get the comments for my table columns. > > > i.e. the ones on my 8.1 database added with this command: > > > COMMENT ON COLUMN addresses.address_id IS 'Unique identifier for the > > addresses table'; > > > thanks > > > Tim- Hide quoted text - > > - Show quoted text - I dont know about that query - I dont have sys_flex_labels but this seems to work: select pc.relname as tablename, pa.attname as column, pd.description from pg_description pd, pg_class pc, pg_attribute pa where pc.relowner = 16403 and pa.attrelid = pc.oid and pd.objoid = pc.oid and pd.objsubid = pa.attnum where I had to figure out the relowner and my schema owner
On Jan 27, 2007, at 8:57 PM, Timasmith wrote: > What query can I run to get the comments for my table columns. > > i.e. the ones on my 8.1 database added with this command: > > COMMENT ON COLUMN addresses.address_id IS 'Unique identifier for the > addresses table'; There's a function that allows you to do that; see the 'System Information Functions' section of the docs. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)