Useful System Table Queries in Relational Databases

SELECT 
 table_schema AS 'Database',
 table_name AS 'Table',
 table_rows AS 'Row Count',
 ROUND((data_length + index_length) / (1024 * 1024), 2) AS 'Total Size (MB)'
FROM
 information_schema.TABLES
WHERE
 table_schema NOT IN ('performance_schema', 'sys’,’mysql', 'information_schema')
ORDER BY
 table_rows DESC;

SELECT
table_schema, -- Database/schema
table_name, -- Table name
index_name, -- Name of the index
column_name, -- Column included in the index
non_unique
FROM
information_schema.statistics
WHERE
table_schema NOT IN ('performance_schema', 'sys’,’mysql', 'information_schema')
ORDER BY
table_name, index_name, seq_in_index;

SELECT 
 constraint_name, -- Name of the FK constraint
 table_name, -- Child table
 column_name, -- Column in child table
 referenced_table_name, -- Parent table
 referenced_column_name 
FROM 
 information_schema.key_column_usage
WHERE 
 referenced_table_name IS NOT NULL -- Only FKs (ignores PKs and unique keys)
ORDER BY 
 table_name, constraint_name;

SELECT 
 id, -- Session/connection ID
 user, -- Query runner
 host, -- From which host
 db, -- Which database is being accessed
 command, -- Type of command (Query, Sleep, etc.)
 time, -- Seconds the thread has been in its current state
 state, -- What it's doing (e.g., Sending data)
 info -- The actual SQL query
FROM 
 information_schema.processlist
WHERE 
 command != 'Sleep' -- Ignore idle connections
ORDER BY 
 time DESC;

SELECT 
 grantee, -- User or role
 table_schema, -- Returns schema
 table_name, -- Returns table name
 privilege_type -- Type of privilege (SELECT, INSERT, etc.)
FROM 
 information_schema.schema_privileges
UNION
SELECT 
 grantee,
 table_schema,
 table_name,
 privilege_type
FROM 
 information_schema.table_privileges
ORDER BY 
 grantee, table_name;

SELECT 
 owner, -- Schema/owner of the table
 table_name, -- Table name
 num_rows -- Estimated number of rows (from statistics)
FROM 
 all_tables
WHERE 
 owner NOT IN ('SYS', 'SYSTEM') -- Exclude system tables
ORDER BY 
 num_rows DESC;

SELECT 
 ind.owner, -- Schema/owner
 ind.index_name, -- Index name
 ind.table_name, -- Indexed table
 col.column_name, -- Indexed column
 col.column_position -- Order in index (for composite indexes)
FROM 
 all_indexes ind
JOIN 
 all_ind_columns col ON ind.index_name = col.index_name AND ind.owner = col.index_owner
WHERE 
 ind.owner NOT IN ('SYS', 'SYSTEM')
ORDER BY 
 ind.table_name, ind.index_name, col.column_position;

SELECT 
 fk_detail.dependent_table AS source_table, -- Table with the foreign key
 fk_detail.dependent_column AS source_column, -- Column containing the reference
 pk_info.independent_table AS target_table, -- Table being referenced
 pk_columns.independent_column AS target_column -- Primary key column being referenced
FROM 
 all_cons_columns fk_detail
INNER JOIN 
 all_constraints relationship_type 
 ON fk_detail.schema_owner = relationship_type.schema_owner 
 AND fk_detail.constraint_identifier = relationship_type.constraint_identifier
INNER JOIN 
 all_constraints pk_info 
 ON relationship_type.reference_owner = pk_info.schema_owner 
 AND relationship_type.reference_constraint = pk_info.constraint_identifier
INNER JOIN 
 all_cons_columns pk_columns 
 ON pk_info.schema_owner = pk_columns.schema_owner 
 AND pk_info.constraint_identifier = pk_columns.constraint_identifier 
 AND fk_detail.column_position = pk_columns.column_position
WHERE 
 relationship_type.constraint_category = 'R' -- Relationships of reference type only
ORDER BY 
 fk_detail.dependent_table, fk_detail.constraint_identifier;

SELECT 
 sessions.session_id, -- Session id
 sessions.user_name, -- Who is running it
 sessions.session_status, -- Active or inactive
 sessions.sql_identifier,
 sessions.hash_value,
 sessions.elapsed_time_since_call AS runtime_seconds, -- Runtime in seconds
 sql_statements.statement_text -- SQL query
FROM 
 v$active_sessions sessions
INNER JOIN 
 v$sql_repository sql_statements 
 ON sessions.sql_identifier = sql_statements.identifier
WHERE 
 sessions.session_status = 'ACTIVE'
ORDER BY 
 sessions.elapsed_time_since_call DESC;

SELECT 
 t.name AS TableName, -- Table name
 SUM(p.rows) AS RowCount -- Total rows from all partitions (indexes)
FROM 
 sys.tables t
JOIN 
 sys.partitions p ON t.object_id = p.object_id
WHERE 
 p.index_id IN (0, 1) -- 0 = heap, 1 = clustered index
GROUP BY 
 t.name
ORDER BY 
 RowCount DESC

SELECT 
 tbl.name AS TableName,
 idx.name AS IndexName,
 col.name AS ColumnName,
 idx.type_desc AS IndexType
FROM 
 sys.tables tbl
JOIN 
 sys.indexes idx ON tbl.object_id = idx.object_id
JOIN 
 sys.index_columns idx_col ON idx.object_id = idx_col.object_id AND idx.index_id = idx_col.index_id
JOIN 
 sys.columns col ON tbl.object_id = col.object_id AND idx_col.column_id = col.column_id
WHERE 
 idx.is_primary_key = 0 
 AND idx.is_unique_constraint = 0
ORDER BY 
 tbl.name, idx.name

SELECT
fk.name , -- Name of the foreign key
parent_tbl.name , 	 -- Foreign key defined table
parent_col.name , -- Parent table column
referenced_tbl.name , 	 -- Table being referenced
referenced_col.name -- Referenced table column
FROM
sys.foreign_keys fk
JOIN
sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
JOIN
sys.tables parent_tbl
ON fkc.parent_object_id = parent_tbl.object_id
JOIN
sys.columns parent_col
ON parent_col.object_id = fkc.parent_object_id
AND parent_col.column_id = fkc.parent_column_id
JOIN
sys.tables referenced_tbl
ON fkc.referenced_object_id = referenced_tbl.object_id
JOIN
sys.columns referenced_col
ON referenced_col.object_id = fkc.referenced_object_id
AND referenced_col.column_id = fkc.referenced_column_id

SELECT 
 r.status, -- 'running', 'suspended', etc.
 r.start_time, -- When it started
 r.command, -- Command type (SELECT, INSERT, etc.)
 t.text AS QueryText -- Actual SQL text
FROM 
 sys.dm_exec_requests r
CROSS APPLY 
 sys.dm_exec_sql_text(r.sql_handle) t
WHERE 
 r.status = 'running'
ORDER BY 
 r.start_time ASC

SELECT 
 dp.name AS DatabasePrincipal,
 dp.type_desc, -- User, Role, etc.
 o.name AS ObjectName,
 p.permission_name,
 p.state_desc -- GRANT, DENY, etc.
FROM 
 sys.database_permissions p
JOIN 
 sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
LEFT JOIN 
 sys.objects o ON p.major_id = o.object_id
ORDER BY 
    dp.name, o.name

SELECT 
 relname AS "Table Name", -- Name of the table
 n_live_tup AS "Estimated Rows" -- Estimated number of live rows
FROM 
 pg_stat_user_tables
ORDER BY 
 n_live_tup DESC;

SELECT 
 t.relname AS TableName, -- Table
 i.relname AS IndexName, -- Index
 a.attname AS ColumnName -- Column used in index
FROM 
 pg_class t,
 pg_class i,
 pg_index ix,
 pg_attribute a
WHERE 
 t.oid = ix.indrelid
 AND i.oid = ix.indexrelid
 AND a.attrelid = t.oid
 AND a.attnum = ANY(ix.indkey)
ORDER BY 
 t.relname, i.relname;

SELECT
conname AS FK_Name,
conrelid::regclass AS Table, -- Child table
att.attname AS Column, -- Column in child
confrelid::regclass AS ReferencedTable -- Parent table
FROM
pg_constraint con
JOIN
pg_attribute att ON att.attrelid = con.conrelid and att.attnum = ANY(con.conkey)
WHERE
contype = 'f'; -- f = fk (foreign key)

SELECT 
 pid AS process_id, -- Process id
 now() - query_start AS duration, -- Run time
 query
FROM 
 pg_stat_activity
WHERE 
 state = 'active'
ORDER BY 
 duration DESC;