Re: Returning schema name with table name

Hi,

my standard query (adapted to 1mb size) is:
select
      t.spcname as "tablespace"
    , pg_get_userbyid(c.relowner) as "owner"
    , n.nspname as "schema"
    , relname::text as "name"
    , pg_size_pretty(pg_total_relation_size(c.oid))::text as "total size"
    , case
        when c.relkind='i' then 'index'
        when c.relkind='t' then 'toast'
        when c.relkind='r' then 'table'
        when c.relkind='v' then 'view'
        when c.relkind='c' then 'composite type'
        when c.relkind='S' then 'sequence'
        else c.relkind::text
      end as "type"
from
    pg_class c
    left join pg_namespace n on n.oid = c.relnamespace
    left join pg_tablespace t on t.oid = c.reltablespace
where
    (pg_total_relation_size(c.oid)>>20)>0 and c.relkind!='t'
order by
    c.relkind desc, pg_total_relation_size(c.oid) desc


Andrus schrieb:
> SELECT oid, relname::char(35) as Table_Name,
>       pg_size_pretty(pg_total_relation_size(oid))::VARCHAR(15) as
> Total_Table_Size
> FROM pg_class
> where  pg_total_relation_size(oid)/(1024*1024)>0
> ORDER BY pg_total_relation_size(oid) desc
>
> returns table names with size greater than 1 MB
>
> How to modify this so that schema name is also returned?
> I have lot of tables with same name and thus this output is difficult
> to understand.
> pg_class seems not contain schema names.
>
> Andrus.
>
>

--
Thomas Markus

====================================================
proventis GmbH | Zimmerstr. 79-81 | D-10117 Berlin |
Tel +49 (0)30 2936399-22 | Fax -50 | t.markus@proventis.net
-----------------------------------------------------------------
Geschäftsführer: Norman Frischmuth | Sitz: Berlin
Handelsregister: AG Berlin-Charlottenburg, HR 82917
-----------------------------------------------------------------
Blue Ant-webbasiertes Projektmanagement - aktuelle Termine 2008:
http://www.proventis.net/website/live/blueant/veranstaltungen.html
====================================================