Обсуждение: How to list all schema names inside a PostgreSQL database through SQL
Hi, How can I list all schema names inside a PostgreSQL database through SQL, especially thoese without any objects created inside it. Regards, Xiaobo Gu
2012/11/15 Xiaobo Gu <guxiaobo1982@gmail.com>: > How can I list all schema names inside a PostgreSQL database through > SQL, especially thoese without any objects created inside it. Something like this: select n.nspname, count(o.oid) from pg_namespace n left join pg_class o on n.oid=o.relnamespace group by 1 order by count(o.oid)>0, 1; I prefer to query PostgreSQL catalogs. You can obtain the same information using information_schema queries. -- Victor Y. Yegorov
Re: How to list all schema names inside a PostgreSQL database through SQL
От
Achilleas Mantzios
Дата:
On Πεμ 15 Îοε 2012 20:31:05 Xiaobo Gu wrote: > Hi, > > How can I list all schema names inside a PostgreSQL database through > SQL, especially thoese without any objects created inside it. > > 1st solution : select catalog_name,schema_name from information_schema.schemata ; 2nd solution : select * from pg_namespace ; > Regards, > > Xiaobo Gu > > > - Achilleas Mantzios IT DEPT
If you are looking for list of empty schema's (No objects in schema), then you can use below query:
select nspname from pg_namespace where oid not in (select relnamespace from pg_class) and oid not in (select oid from pg_proc);
Regards,
Baji Shaik.
On Thu, Nov 15, 2012 at 6:13 PM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
On Πεμ 15 Ποε 2012 20:31:05 Xiaobo Gu wrote:1st solution :
> Hi,
>
> How can I list all schema names inside a PostgreSQL database through
> SQL, especially thoese without any objects created inside it.
>
>
select catalog_name,schema_name from information_schema.schemata ;
2nd solution :
select * from pg_namespace ;
> Regards,
>
> Xiaobo Gu
>
>
>
-
Achilleas Mantzios
IT DEPT
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi, On 15 November 2012 23:31, Xiaobo Gu <guxiaobo1982@gmail.com> wrote: > How can I list all schema names inside a PostgreSQL database through > SQL, especially thoese without any objects created inside it. Use -E psql's option: -E, --echo-hidden display queries that internal commands generate then you get SQL query for each internal command. The second option is to use information_schema.schemata view (this is works across databases) -- Ondrej Ivanic (http://www.linkedin.com/in/ondrejivanic)
Thanks a lot. On Fri, Nov 16, 2012 at 5:33 AM, Ondrej Ivanič <ondrej.ivanic@gmail.com> wrote: > Hi, > > On 15 November 2012 23:31, Xiaobo Gu <guxiaobo1982@gmail.com> wrote: >> How can I list all schema names inside a PostgreSQL database through >> SQL, especially thoese without any objects created inside it. > > Use -E psql's option: > -E, --echo-hidden display queries that internal commands generate > > then you get SQL query for each internal command. > > The second option is to use information_schema.schemata view (this is > works across databases) > > > -- > Ondrej Ivanic > (http://www.linkedin.com/in/ondrejivanic)
Thanks a lot. On Fri, Nov 16, 2012 at 5:33 AM, Ondrej Ivanič <ondrej.ivanic@gmail.com> wrote: > Hi, > > On 15 November 2012 23:31, Xiaobo Gu <guxiaobo1982@gmail.com> wrote: >> How can I list all schema names inside a PostgreSQL database through >> SQL, especially thoese without any objects created inside it. > > Use -E psql's option: > -E, --echo-hidden display queries that internal commands generate > > then you get SQL query for each internal command. > > The second option is to use information_schema.schemata view (this is > works across databases) > > > -- > Ondrej Ivanic > (http://www.linkedin.com/in/ondrejivanic)