Get all tables under a database in Postgre
-
I need to get all database names, using SQL script only. I have tried:
SELECT datname FROM pg_database WHERE datistemplate = false
and seem to work. I got:
mydb1
mydb2
postgresmybd1 and mydb2 have been created by me. So far, so good. Now, I need to find all tables under every database, using SQL script only. Is it possible ?
-
_Flaviu wrote:
As far as I understood, "use ..." is not valid command in Postgre SQL
I know. But did you try the
\connect
-
Did you try the link I gave you at Re: List all tables from a specific database - Database Discussion Boards[^]?
Yes, I have tried:
SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND
schemaname != 'information_schema';but this script doesn't take into account a specific database. I don't understand how is relation between database and table, in Postgre SQL ...
-
Yes, I have tried:
SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND
schemaname != 'information_schema';but this script doesn't take into account a specific database. I don't understand how is relation between database and table, in Postgre SQL ...
-
That command is a Postgre SQL cmd line command, I cannot run it as SQL script, I have tried that. Thank you.
-
Could you try to set the desired database in the connection string ([PostgreSQL ODBC Driver (psqlODBC) Connection Strings - ConnectionStrings.com](https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/))?
-
That is not what the tutorial suggests. The summary states:
Quote:
Summary: in this tutorial, you will learn how to show tables in PostgreSQL using psql tool and pg_catalog schema.
-
I realize that all available tables are below existing connection, which is the default database. And the default database must be choosen at the connection time. But yes, all databases could be listed in any connection. Thank you for all your support.
If you have problems with connection to database, you can try different tools. For example .net data provider for postgresql.
-
Could it help you: [sql - List all tables in postgresql information_schema - Stack Overflow](https://stackoverflow.com/questions/2276644/list-all-tables-in-postgresql-information-schema) ?
SELECT TABLE_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_CATALOG=$dbname AND T.TABLE_SCHEMA='public';
-
SELECT TABLE_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_CATALOG=$dbname AND T.TABLE_SCHEMA='public';
Get create table statements for a database under a schema.
SELECT TABLE_NAME, 'CREATE TABLE '||TABLE_NAME|| ' ('||STRING_AGG(CONCAT( C1, C2, C3, C4, C5, C6 ),', ')||')' AS QUERY FROM ( SELECT C.TABLE_NAME, '"'||C.COLUMN_NAME||'"' || ' ' || CASE WHEN DATA_TYPE='ARRAY' THEN LTRIM(UDT_NAME,'_')||'[]' ELSE DATA_TYPE END AS C1, CASE WHEN CHARACTER_MAXIMUM_LENGTH > 0 THEN '(' || CHARACTER_MAXIMUM_LENGTH || ')' END AS C2, CASE WHEN NUMERIC_PRECISION > 0 AND NUMERIC_SCALE < 1 THEN NULL END AS C3, CASE WHEN NUMERIC_PRECISION > 0 AND NUMERIC_SCALE > 0 THEN NULL END AS C4, CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' END AS C5, CASE WHEN COLUMN_DEFAULT IS NOT NULL AND COLUMN_DEFAULT NOT LIKE 'nextval%' THEN ' DEFAULT' END || ' ' || REPLACE(COLUMN_DEFAULT, '::CHARACTER VARYING', '') AS C6 FROM INFORMATION_SCHEMA.COLUMNS C, INFORMATION_SCHEMA.TABLES T WHERE C.TABLE_CATALOG='tpch' AND T.TABLE_CATALOG='tpch' AND T.TABLE_SCHEMA='public' AND C.TABLE_NAME=T.TABLE_NAME AND C.TABLE_SCHEMA='public' AND T.TABLE_TYPE IN ('BASE TABLE') ORDER BY C.TABLE_NAME, C.ORDINAL_POSITION ) AS STRING_COLUMNS GROUP BY TABLE_NAME