Thread: SQL Command - To List Tables ?
Is there a SQL command I can issue which will list all the TABLES within a database ? Pete
Peter Moscatt wrote: >Is there a SQL command I can issue which will list all the TABLES within >a database ? > >Pete > > Well, when you are the postgres commandline client, you can type # \dt That will show all the tables in your current database. -- Robby Russell, | Sr. Administrator / Lead Programmer Command Prompt, Inc. | http://www.commandprompt.com rrussell@commandprompt.com | Telephone: (503) 222.2783
On Wednesday, August 27, 2003, at 07:57 AM, Robby Russell wrote:
> Peter Moscatt wrote:
>
>> Is there a SQL command I can issue which will list all the TABLES
>> within
>> a database ?
>>
>> Pete
>>
>
> Well, when you are the postgres commandline client, you can type
> # \dt
>
> That will show all the tables in your current database.
>
If you would actually like to issue a command, you can start psql with
the -E option. So it will show you all the queries it uses:
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
Which of course you can pare down to get just the information you want.
Jeff
Hello. I type \dt as user postgres, but it show only the tables within public schema. I need to know all tables (like v$_ or dba_ or user_ in oracle). How I do that? Thanks. Robby Russell wrote: > Peter Moscatt wrote: > >Is there a SQL command I can issue which will list all the TABLES within > >a database ? > > Well, when you are the postgres commandline client, you can type > # \dt > > That will show all the tables in your current database.
If you start psql with the -E option you can see the internal commands sent to
the backend. This can often give you a lot of hints as to the best way to
pull catalog data from a db:
jason=# \dt
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
So from this to get a list of tables you would execute the following in SQL:
select c.relname FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid);
This will give you tables only. If you wanted schema's and owners then you
would execute a similar variant to that thrown out by psql.
Rgds,
Jason
On Wed, 27 Aug 2003 08:37 pm, Peter Moscatt wrote:
> Is there a SQL command I can issue which will list all the TABLES within
> a database ?
>
> Pete
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
On Wed, Aug 27, 2003 at 20:37:14 +1000, Peter Moscatt <pgmoscatt@optushome.com.au> wrote: > Is there a SQL command I can issue which will list all the TABLES within > a database ? select datname from pg_database;
On Wed, 2003-08-27 at 05:37, Peter Moscatt wrote: > Is there a SQL command I can issue which will list all the TABLES within > a database ? http://www.postgresql.org/docs/7.3/static/reference.html http://www.postgresql.org/docs/7.3/static/reference-client.html http://www.postgresql.org/docs/7.3/static/app-psql.html OR http://www.postgresql.org/docs/7.3/static/reference.html http://www.postgresql.org/search.cgi?ul=http%3A%2F%2Fwww.postgresql.org%2Fdocs%2F7.3%2Fstatic%2F&q=list+all+tables -- ----------------------------------------------------------------- Ron Johnson, Jr. ron.l.johnson@cox.net Jefferson, LA USA "Man, I'm pretty. Hoo Hah!" Johnny Bravo
> Is there a SQL command I can issue which will list all the TABLES within > a database ? select datname from pg_database; -- WBR, sector119
On 27/08/2003 11:37 Peter Moscatt wrote: > Is there a SQL command I can issue which will list all the TABLES within > a database ? > > Pete select tablename from pg_tables HTH -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
> Is there a SQL command I can issue which will list all the TABLES within > a database ? If you want to get database-specific attributes, such as tables, views, etc... just try \? from the psql monitor, and you will get a list of nice functions. '\dt' will list all tables, for example. Thanks --mathan
On 27 Aug 2003 at 20:37, Peter Moscatt wrote: > Is there a SQL command I can issue which will list all the TABLES within > a database ? \dt from psql or select relname from pg_class; Check the column name. There might be a typo there but pg_class is what you are looking at. Bye Shridhar -- There are certain things men must do to remain men. -- Kirk, "The Ultimate Computer", stardate 4929.4
Carmen Gloria Sepulveda Dedes <csepulveda@atichile.com> writes:
> Hello. I type \dt as user postgres, but it show only the tables within
> public schema.
More accurately, it shows the tables visible in your schema search path.
> I need to know all tables
\dt *.*
regards, tom lane
On Wed, Aug 27, 2003 at 08:09:16 -0500, Bruno Wolff III <bruno@wolff.to> wrote: > On Wed, Aug 27, 2003 at 20:37:14 +1000, > Peter Moscatt <pgmoscatt@optushome.com.au> wrote: > > Is there a SQL command I can issue which will list all the TABLES within > > a database ? > > select datname from pg_database; This was an oops. I misread your question as asking for all databases, not tables.
On Wednesday, August 27, 2003, at 09:17 AM, Carmen Gloria Sepulveda
Dedes wrote:
> Hello. I type \dt as user postgres, but it show only the tables within
> public schema. I need to know all tables (like v$_ or dba_ or user_
> in oracle). How I do that?
Start psql with the echo flag:
[inligo:~] heath% psql -E dpe
********* QUERY **********
BEGIN; SELECT usesuper FROM pg_catalog.pg_user WHERE usename = 'heath';
COMMIT
**************************
Welcome to psql 7.3.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
dpe=# \dt
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
-heath
On Wed, Aug 27, 2003 at 09:17:01AM -0400, Carmen Gloria Sepulveda Dedes wrote: > Hello. I type \dt as user postgres, but it show only the tables within > public schema. I need to know all tables (like v$_ or dba_ or user_ > in oracle). How I do that? You need to change your search_path. Use SET search_path TO 'one-schema', 'another', ... Then repeat your \dt Or IIRC you can also use \dt *.* -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "La espina, desde que nace, ya pincha" (Proverbio africano)
On Wed, 2003-08-27 at 21:03, Heath Tanner wrote:
> On Wednesday, August 27, 2003, at 09:17 AM, Carmen Gloria Sepulveda
> Dedes wrote:
>
> > Hello. I type \dt as user postgres, but it show only the tables within
> > public schema. I need to know all tables (like v$_ or dba_ or user_
> > in oracle). How I do that?
>
> Start psql with the echo flag:
>
> [inligo:~] heath% psql -E dpe
> ********* QUERY **********
> BEGIN; SELECT usesuper FROM pg_catalog.pg_user WHERE usename = 'heath';
> COMMIT
> **************************
>
> Welcome to psql 7.3.4, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
> \h for help with SQL commands
> \? for help on internal slash commands
> \g or terminate with semicolon to execute query
> \q to quit
>
> dpe=# \dt
> ********* QUERY **********
> SELECT n.nspname as "Schema",
> c.relname as "Name",
> CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
> THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
> "Type",
> u.usename as "Owner"
> FROM pg_catalog.pg_class c
> LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
> LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('r','')
> AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
> AND pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY 1,2;
> **************************
Isn't that big CASE statement unnecessary, since you also have
c.relkind IN ('r','')
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA
484,246 sq mi are needed for 6 billion people to live, 4 persons
per lot, in lots that are 60'x150'.
That is ~ California, Texas and Missouri.
Alternatively, France, Spain and The United Kingdom.
On Thu, Aug 28, 2003 at 04:31:16AM -0500, Ron Johnson wrote:
>
> Isn't that big CASE statement unnecessary, since you also have
> c.relkind IN ('r','')
Not really. In the code almost all the \d commands use that same query but
just change the "relkind in" clause.
So you can see it's redundant on a local level but the code that generates
it doesn't need to know.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato
Attachment
How about
SELECT * FROM pg_tables;
optionally add:
WHERE schemaname != 'pg_catalog'
AND schemaname != 'information_schema'
Willy-Bas Loos
>If you start psql with the -E option you can see the internal commands sent to
>the backend. This can often give you a lot of hints as to the best way to
>pull catalog data from a db:
>
>jason=# \dt
>********* QUERY **********
>SELECT n.nspname as "Schema",
> c.relname as "Name",
> CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
>'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
> u.usename as "Owner"
>FROM pg_catalog.pg_class c
> LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
> LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
>WHERE c.relkind IN ('r','')
> AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
> AND pg_catalog.pg_table_is_visible(c.oid)
>ORDER BY 1,2;
>**************************
>
>So from this to get a list of tables you would execute the following in SQL:
>
>select c.relname FROM pg_catalog.pg_class c
>LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
>WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
>AND pg_catalog.pg_table_is_visible(c.oid);
>
>This will give you tables only. If you wanted schema's and owners then you
>would execute a similar variant to that thrown out by psql.
>
>Rgds,
>
>Jason
>
>On Wed, 27 Aug 2003 08:37 pm, Peter Moscatt wrote:
>> Is there a SQL command I can issue which will list all the TABLES within
>> a database ?
>>
>> Pete
>>
>>
>>
>