Useful System Table Queries in Relational Databases

System tables store metadata about database objects like tables, columns, indexes, users, and privileges, which helps in understanding the configuration.

Dhaval Patolia

May. 27, 25 · Analysis

Likes (1)

Comment

Save

2.3K Views

Overview

System tables are managed by the DBMS (Database Management System), which provides visibility into how the databases work internally. In this article, I will go over the system tables and their use cases for SQL Server, Oracle, PostgreSQL, and MySQL databases.

MySQL Queries

1. See how large each table is in terms of rows to identify large data sets or usage hotspots.

    MySQL
   
 

   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;
  

2. Examine how tables are indexed, which is key for query optimization. You can identify duplicate or missing indexes.

    MySQL
   
 

   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;
  

3. Understand table relationships, integrity constraints, and cascade rules in your schema design.

    MySQL
   
 

   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;

  

4. Detect long-running or potentially blocking queries for performance monitoring.

    MySQL
   
 

   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;
  

5. Audit who has access to what is essential for security reviews and least-privilege enforcement.

    MySQL
   
 

   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;
  

Oracle Queries

1. Identify the largest tables for storage, indexing, or archiving. NUM_ROWS depends on the last ANALYZE or DBMS_STATS.

    PLSQL
   
 

   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;

  

2. Help in index optimization and validation, showing which indexes exist and what columns they cover.

    PLSQL
   
 

   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;

  

3. Track dependencies and ensure relational integrity, especially helpful for ERD design or cascading updates.

    PLSQL
   
 

   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;
  

4. Performance tuning — track long-running SQL statements, pinpoint slow queries, or blocking sessions.

    PLSQL
   
 

   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;
  

SQL Server Queries

1. Find the largest tables by row count for performance and space analysis. This is faster than COUNT(*) because it uses metadata.

    MS SQL
   
 

   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

  

2. Review indexes (excluding primary keys/constraints) to identify redundancy, missing indexes, or performance tuning opportunities.

    MS SQL
   
 

   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
  

3. Identify how tables are related via foreign keys — essential for understanding data relationships and joins.

    MS SQL
   
 

   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
  

4. Monitor currently running queries — useful for diagnosing bottlenecks or runaway processes.

    MS SQL
   
 

   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

  

5. Security auditing — who has access to what, and how. Use this to verify compliance with least-privilege principles.

    MS SQL
   
 

   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
  

PostgreSQL Queries

1. Quickly estimate table sizes. n_live_tup is from PostgreSQL’s internal stats collector — it's fast but approximate.

    SQL
   
 

   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;
  

2. Reveal which columns are indexed — critical for performance tuning, especially on large tables.

    SQL
   
 

   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;
  

3. Discover relational links (FKs) between tables — useful for reverse-engineering schemas or building ER diagrams.

    SQL
   
 

   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)
  

4. List queries actively running, ordered by duration — helps diagnose slowness or blocking issues.

    SQL
   
 

   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;
  

Conclusion

Incorporating these queries into regular maintenance routines enables data-driven decision making as databases grow. Remember to run complex system queries during off-peak hours to minimize performance impact on production systems.

Database MySQL Relational database sql

Opinions expressed by DZone contributors are their own.

Related

Trending