473,320 Members | 1,838 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL Command - To List Tables ?

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

Nov 11 '05 #1
9 130308
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #2
On Wed, Aug 27, 2003 at 20:37:14 +1000,
Peter Moscatt <pg*******@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;

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #3
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/s...reference.html
http://www.postgresql.org/docs/7.3/s...ce-client.html
http://www.postgresql.org/docs/7.3/static/app-psql.html

OR

http://www.postgresql.org/docs/7.3/s...reference.html
http://www.postgresql.org/search.cgi...ist+all+tables
--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA

"Man, I'm pretty. Hoo Hah!"
Johnny Bravo
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #4
> Is there a SQL command I can issue which will list all the TABLES within
a database ?


select datname from pg_database;

--
WBR, sector119
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #5

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 |
+------------------------------+---------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #6
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #7
Carmen Gloria Sepulveda Dedes <cs********@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #8
On Wed, Aug 27, 2003 at 08:09:16 -0500,
Bruno Wolff III <br***@wolff.to> wrote:
On Wed, Aug 27, 2003 at 20:37:14 +1000,
Peter Moscatt <pg*******@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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #9
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)

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Wayne Wengert | last post by:
I am using VS.NET 2003, VB.NET, ADO.NET and an Access 2000 database. I want to display a list of all tables in an Access database. I want to put that list of table names in a listbox so the user...
3
by: Davide Satta | last post by:
Where can i find a list of command of the standard library of c++ and the explanation of every single command? please help me -- Love & Peace Respect the Nature
1
by: Mariusz Kaczmarczyk | last post by:
Hello, i can't find any matching answer for this question. How to construct SQL query to list all tables in current database? Let's say i use the default view. Or maybe there is better way in PHP...
2
by: Pascal Deparis | last post by:
Hi, I've got a problem with the DB2 Command Line Processor, in V8, FP1. The databases have been created on an AIX server (version 4.3.3). Have a look at this: >db2 ? list DB21034E The...
3
by: maricel | last post by:
Is there a way to list (using db2 command or catalogs) to list hierarchy of table parent & child relationship: 1) A list that shows which table should be deleted first,second,third... 2) A list...
0
by: master | last post by:
Hi, I am new to SQL CE. How to get the list of tables in a database? Thanks for your help... DW
2
by: Danny | last post by:
Hello, We imported a bunch of tables from a database and realized that the primary keys weren't copied to the destination db. In order to re- create the keys, we need to know which tables have...
0
by: ebindia0041 | last post by:
Any help what is wrong with this code.. I am tring to FTP post a file by command prompt but it not work, system hang up.. commands are in my batch file... System.Diagnostics.ProcessStartInfo psi...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.