# SQL

##### PSQL get version

```
psql -h <IP/DNS> -U <user> -d <database> -c 'SELECT VERSION()'
```

##### PSQL test connection

Install packages postgresql-client-common and postgresql-client-10

```
psql -h <host> -p <port> -U <username> -d <database> -c '\c'
```

##### Database size

`SELECT pg_size_pretty( pg_database_size('<database>') );`

##### Table sizes

```
select table_name, pg_size_pretty( pg_relation_size(quote_ident(table_name)) )
from information_schema.tables
where table_schema = 'public'
order by pg_relation_size(quote_ident(table_name)) desc;
```

##### Mysql change user password

```
mysql -u username-p
use mysql;
update user set password=PASSWORD('your_new_password') where User='username';
flush privileges;
quit
```

##### Postgres automatically fit records to the width of the screen(add to ~/.psqlrc to start psql with it every time)

`\x auto`

##### Postgres query from bash

`psql -U <username> -d <database> -c "select * from ppl;"`

##### Mssql list all tables

`select * from sys.all_objects where type = 'U'`

##### Oracle list all users

`select username from dba_users`