Database Useful SQLs

sql.sql

-- Redshift - Query usage --
select
  sum(capacity)/1024 as capacity_gbytes, 
  sum(used)/1024 as used_gbytes, 
  (sum(capacity) - sum(used))/1024 as free_gbytes 
from 
  stv_partitions where part_begin=0;
  
-- usage by tables --
  
SELECT tbl, name, size_mb FROM
(
  SELECT tbl, count(*) AS size_mb
  FROM stv_blocklist
  GROUP BY tbl
)
LEFT JOIN
(select distinct id, name FROM stv_tbl_perm)
ON id = tbl
ORDER BY size_mb DESC
LIMIT 10;

  

-- How to find table size
select segment_name,segment_type,bytes/1024/1024 MB
from dba_segments
where segment_type='TABLE' and segment_name='<yourtablename>';


-- search for constraints of certain table
SELECT *
FROM user_constraints
WHERE table_name = '<your table name>';
-- note: replace 'all_constraints' for all tables outside your ownership


-- Describe a table structure
DESC <table_name>