Re: List all tables from a specific database - Database Discussion Boards

Re: List all tables from a specific database

k505412-Jan-22 5:13

k5054

12-Jan-22 5:13

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>

Re: List all tables from a specific database

_Flaviu12-Jan-22 5:32

_Flaviu

12-Jan-22 5:32

"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.

Re: List all tables from a specific database

Richard MacCutchan12-Jan-22 5:39

Richard MacCutchan

12-Jan-22 5:39

PostgreSQL Show Tables[^]

Re: List all tables from a specific database

_Flaviu12-Jan-22 6:19

_Flaviu

12-Jan-22 6:19

Thank you Richard. I have tried before, and now, the SQL from that page:

SQL

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[^]

Re: List all tables from a specific database

Richard MacCutchan12-Jan-22 6:41

Richard MacCutchan

12-Jan-22 6:41

But that is exactly what that command is supposed to do. You need to follow the instructions from the top of the page.

Re: List all tables from a specific database

_Flaviu12-Jan-22 7:16

_Flaviu

12-Jan-22 7:16

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:

SQL

USE database my_database

but seem to not work:

SQL

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.

Re: List all tables from a specific database

Richard MacCutchan12-Jan-22 7:43

Richard MacCutchan

12-Jan-22 7:43

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.

Re: List all tables from a specific database

Sakshi Jain 202213-Jul-22 0:18

Sakshi Jain 2022

13-Jul-22 0:18

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.*

WampServer localhost

_Flaviu6-Jan-22 7:59

_Flaviu

6-Jan-22 7:59

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.

Re: WampServer localhost

Graham Breach6-Jan-22 22:10

Graham Breach

6-Jan-22 22:10

The web server is "Apache" - make sure you start that, then the web-based admin things should work (assuming it is configured correctly).

Re: WampServer localhost

_Flaviu7-Jan-22 0:53

_Flaviu

7-Jan-22 0:53

Ohh, I tried that before, but didn't start it:
Untitled5 — ImgBB[^]

I'll check the log to see why.

SQLite sqlite_schema missing

_Flaviu4-Jan-22 5:20

_Flaviu

4-Jan-22 5:20

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:

SQL

SELECT * FROM sqlite_schema WHERE type = 'table' AND name NOT LIKE 'sqlite_%'

I got:

SQL

no such table: sqlite_schema

I also tried to create a database on my own:

C++

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

C++

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:

SQL

SELECT * FROM sqlite_schema WHERE type = 'table' AND name NOT LIKE 'sqlite_%'

I got the same error:

SQL

no such table: sqlite_schema

How can I assure to retrieve all tables from a db3 (db) file ?

Re: SQLite sqlite_schema missing

Richard Deeming4-Jan-22 5:40

Richard Deeming

4-Jan-22 5:40

SQLite Forum: Documentation issue (wrong table name) in FAQ, point 7[^]
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

Re: SQLite sqlite_schema missing

_Flaviu4-Jan-22 8:16

_Flaviu

4-Jan-22 8:16

Thanks a lot. That was the issue.

I have a small request: can you share which structure has this table, sqlite_master ?

I need a database_id, table_id, or appropriate.

Re: SQLite sqlite_schema missing

_Flaviu4-Jan-22 8:17

_Flaviu

4-Jan-22 8:17

Ahh, I think I have found it: SQLite: sqlite_master (internal schema object)[^]

Date and Time Add Incompatibility between sql 2012 and sql 2018

Medo-I23-Dec-21 8:42

Medo-I

23-Dec-21 8:42

Hello all

i have stored procedure running fine in MS sql 2012 but when i run it in ms sql 2018 i got this error message:

The data types datetime and time are incompatible in the add operator.

this is the line of the error :

SQL

(CONVERT(datetime, shfts.Date) + SftpStartTime) as SftStartDateTime,
case when ((CONVERT(datetime, shfts.Date) +SftpEndTime) 
			> (CONVERT(datetime, shfts.Date) + SftpStartTime) )

Re: Date and Time Add Incompatibility between sql 2012 and sql 2018

Richard Andrew x6423-Dec-21 9:56

Richard Andrew x64

23-Dec-21 9:56

I think you'll have to use the DATEADD function instead of just the addition operator.

The difficult we do right away...
...the impossible takes slightly longer.

Re: Date and Time Add Incompatibility between sql 2012 and sql 2018

CHill6029-Dec-21 6:31

CHill60

29-Dec-21 6:31

Your problem is occurring because you cannot add time to a date using columns or variables - although you can do it with literals - but only if you convert the date to a datetimeThis is valid - but doesn't help you I know, just putting it out there

SQL

select convert(datetime, shftsDate) + '16:12:00'

SftStartDateTime is obviously just a time so you can't use the DateAdd function - if you try you will get another error

Quote:
Argument data type time is invalid for argument 2 of dateadd function.

Converting that time into "seconds from midnight" could be an option, but that is a real PITA - Google for it and see how awful the solutions are Smile | :)

The solution is to ensure that both operands of are of the same type. E.g.

SQL

select convert(datetime, @shftsDate) + convert(datetime, @SftpStartTime);

Re: Date and Time Add Incompatibility between sql 2012 and sql 2018

jschell29-Dec-21 8:34

jschell

29-Dec-21 8:34

Medo-I wrote:
SftpStartTime

Kind of curious if this is just a 'time' (no date.) If so then I would say the code is broken regardless.

I am more curious why it would fail for one version. Googling did not provide an answer but it does provide similar problems.

The data types datetime and time are incompatible in the add operator[^]

sql - Add datetime and time - Stack Overflow[^]

Since the example does not provide the values being used perhaps it works for some values and not others.

Re: Date and Time Add Incompatibility between sql 2012 and sql 2018

Richard Deeming4-Jan-22 1:44

Richard Deeming

4-Jan-22 1:44

Check your database compatibility level - it's likely that you had it set to 100 on your SQL Server 2012 instance, but it's set to 110 or higher on your SQL Server 2016/2019 instance.

(NB: There is no SQL Server 2018 version.)

As a temporary workaround, you may be able to set the compatibility level back to 100. But this will prevent you from using any features introduced after SQL Server 2008.

The correct fix would be to change your code to use DATEADD instead:

SQL

DATEADD(second, DATEDIFF(second, 0, SftpStartTime), shfts.Date) As SFtStartDateTime,

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Roelf

floris rudolf vieira22-Dec-21 7:58

floris rudolf vieira

22-Dec-21 7:58

Is this app like 3rd party cookies?

Re: Roelf

jschell23-Dec-21 7:12

jschell

23-Dec-21 7:12

More like chocolate chip

Re: Roelf

0x01AA5-Jan-22 5:44

0x01AA

5-Jan-22 5:44

Don't get you question. Can you please elaborate?

SQL SERVER

ali khan Dec202118-Dec-21 23:36

ali khan Dec2021

18-Dec-21 23:36

a network-related or instance-specific error occured while establishing a connection to SQL Server.
the server was not found or was not accessible. very that the instance name is correct and that SQL Server 
is configured to allow remote connections.(Provider:Names pipes provider, error:40 - could not open a connection
to sql server ) (Microsoft sql server, error:53)

Re: SQL SERVER

Richard MacCutchan19-Dec-21 7:57

Richard MacCutchan

19-Dec-21 7:57

Please stop reposting this message. You have been told more than once what you need to do to resolve this.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.