= Chaetsheet Databases =

**Summary**: Database hints, tips, oneliners and best practices. \\
**Date**: 8 December 2024 \\
{{tag>cheatsheet sql}}

== MS SQL ==

> Start MS SQL Management Studio as a user from a different domain to a different database server

<code dos>
runas /netonly /user:getshifting.local\admin-sjoerd "c:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe -S dbdev01.getshifting.local"
</code>
\\
> See all logged in users
<code sql>
CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255),
      BlkBy  VARCHAR(255),DBName  VARCHAR(255),
      Command VARCHAR(255),CPUTime INT,
      DiskIO INT,LastBatch VARCHAR(255),
      ProgramName VARCHAR(255),SPID2 INT,
      REQUESTID INT)
INSERT INTO #sp_who2 EXEC sp_who2
SELECT      *
FROM        #sp_who2
-- Add any filtering of the results here :
WHERE       Login = 'SqlAdmin'
-- Add any sorting of the results here :
-- ORDER BY    DBName ASC

DROP TABLE #sp_who2
</code>
\\
> See all sessions
<code sql>
SELECT s.session_id
    ,r.STATUS
    ,r.blocking_session_id 'blocked by'
    ,r.wait_type
    ,wait_resource
    ,r.wait_time / (1000.0) 'Wait Time (in Sec)'
    ,r.cpu_time
    ,r.logical_reads
    ,r.reads
    ,r.writes
    ,r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)'
    ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
            (
                CASE r.statement_end_offset
                    WHEN - 1
                        THEN Datalength(st.TEXT)
                    ELSE r.statement_end_offset
                    END - r.statement_start_offset
                ) / 2
            ) + 1) AS statement_text
    ,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
     Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
    ,r.command
    ,s.login_name
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.last_request_end_time
    ,s.login_time
    ,r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
    ,r.STATUS
    ,r.blocking_session_id
    ,s.session_id
</code>

== PostgreSQL ==

<code bash>
# get postgresql user and password
cd /data/docker
cat .env
docker container ps
docker exec -it container_db /bin/bash
psql -U <user> -W
</code>
\\

> See connections

<code sql>
show max_connections;
show shared_buffers;
</code>
\\

> See current activity

<code sql>
select pid as process_id,
       usename as username,
       datname as database_name,
       client_hostname,
       application_name,
       backend_start,
       state,
       wait_event,
       state_change
from pg_stat_activity;
</code>
\\

> See all databases
<code sql>
\l
-- See all databases with more information
\l+
</code>