|
|
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.
<pre lang="SQL"> 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
|
|
|
|
|
|
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 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.
modified 16-Jan-22 5:05am.
|
|
|
|
|
How can I list all tables from a specific database in PostGre SQL ?
Lets say I have 3 databases, and I have several tables in every one each of them. How can I retrieve the table names for every database I have, using SQL command ?
With following command:
SELECT datname FROM pg_database WHERE datistemplate = false
I got:
db_name
post_flaviu
postgres
test
Now, how can I find all tables under eevry db from above ?
modified 12-Jan-22 7:16am.
|
|
|
|
|
Feeding "postgresql list tables in database" into your search engine will produce a zillion useful answers.
Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
|
|
|
|
|
Yes, I did. But I haven't found any SQL to tell me the tables for a specific database only.
|
|
|
|
|
You can get Postgres to tell you how to do that using the -E (display hidden queries) option to psql. So for example
$ psql -E -l
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
<hr />
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+-----------+-------------+-------------+-----------------------
template0 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | ebacon | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 |
(3 rows) Likewise for the
\dt<dt> command inside psql. Then all you need to do is to figure out how to combine both queries into one.<br />
<div class="signature">Keep Calm and Carry On</div></dt>
|
|
|
|
|
Thank you. That query retrieve all databases. And for all tables from a database only ?
|
|
|
|
|
try
psql -E test -c "\dt". You can then take that output and combine with the previous one to get a listing of all tables across all databases.<br />
<div class="signature">Keep Calm and Carry On</div>
|
|
|
|
|
"get a listing of all tables across all databases"
That I already did. But doesn't help me, because I need the tables below a specific database, not all tables from all databases.
|
|
|
|
|
|
Thank you Richard. I have tried before, and now, the SQL from that page:
SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
And as result I got all tables, from ALL databases.
Untitled6 — ImgBB[^]
|
|
|
|
|
But that is exactly what that command is supposed to do. You need to follow the instructions from the top of the page.
|
|
|
|
|
I am pretty close. And what SQL command should I use to select the database, and after that to call SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema' ?
I have tried:
USE database my_database
but seem to not work:
ERROR: syntax error at or near "USE";
Error while executing the query
You know, I need to all this programatically, using SQL commands, not using Postgre tools.
modified 12-Jan-22 12:30pm.
|
|
|
|
|
Sorry, I do not have a system available to test this. However, I do not see any instruction concerning a USE statement in the tutorial on that page.
|
|
|
|
|
1. Using SQL Query
To show the list of tables with the corresponding schema name, run this statement:
SELECT * FROM information_schema.tables;
or in a particular schema:
SELECT * FROM information_schema.tables WHERE table_schema = 'schema_name';
2. Using psql
To list all tables:
In all schemas:
\dt *.*
In a particular schema:
\dt schema_name.*
|
|
|
|
|
I have installed a WampServer, and when I wanted to try it, seem that localhost is not available:
Untitled4 — ImgBB[^]
I noticed that mysql and mariadb services are running:
Untitled2 — ImgBB[^]
I only started localhost from here:
Untitled3 — ImgBB[^]
Also, other options from that menu are not working either: PHPMyAdmin, etc. What I should do to use MySQL database ? In fact, I only need to setup a ODBC source for MySQL database from WampServer, that's all.
modified 7-Jan-22 2:58am.
|
|
|
|
|
The web server is "Apache" - make sure you start that, then the web-based admin things should work (assuming it is configured correctly).
|
|
|
|
|
Ohh, I tried that before, but didn't start it:
Untitled5 — ImgBB[^]
I'll check the log to see why.
|
|
|
|
|
How can I get the number of the tables from a sqlite3 database ?
I downloaded from here: SQLite Sample Database And Its Diagram (in PDF format)[^]
chinook.db
But when I try to get all tables from this database:
SELECT * FROM sqlite_schema WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
I got:
no such table: sqlite_schema
I also tried to create a database on my own:
SQLite::Database db("C:\\Temp\\mydb.bd3", SQLite::OPEN_READWRITE | SQLite::OPEN_CREATE);
db.exec(_T("CREATE TABLE sqlite_schema(type TEXT, name TEXT, tbl_name TEXT, rootpage INTEGER, sql TEXT)"));
db.exec(_T("PRAGMA foreign_keys = ON"));
and
db.exec(_T("DROP TABLE IF EXISTS master"));
db.exec(_T("CREATE TABLE master(masterid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT)"));
db.exec(_T("DROP TABLE IF EXISTS app"));
db.exec(_T("CREATE TABLE app (appid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT)"));
db.exec(_T("DROP TABLE IF EXISTS device"));
db.exec(_T("CREATE TABLE device (deviceid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, masterid INTEGER, appid INTEGER, name TEXT, FOREIGN KEY(masterid) REFERENCES master(masterid), FOREIGN KEY(appid) REFERENCES app(appid))"));
but when I try the same SQL:
SELECT * FROM sqlite_schema WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
I got the same error:
no such table: sqlite_schema
How can I assure to retrieve all tables from a db3 (db) file ?
|
|
|
|
|
The name has been changed to "sqlite_schema" as of version 3.33.0. The older name "sqlite_master" is still accepted as an alias for backwards compatibility. The FAQ is for version 3.33.0. You must be using an older version of SQLite.
Assuming you're not using 3.33.0 or later, try using the sqlite_master table instead.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
