如何在Oracle SQL Developer中快速筛选出所有非空数据表

以下是不同主流数据库的可用查询语句，可直接筛选出所有含数据的业务表，无需逐张编写查询：

MySQL #

快速估算版本（适合大库快速筛查，InnoDB引擎下为统计估算值，误差极小） #

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS 
FROM information_schema.TABLES 
WHERE 
  TABLE_SCHEMA = '替换为你的数据库名称' 
  AND TABLE_TYPE = 'BASE TABLE'
  AND TABLE_ROWS > 0;

100%精确版本（遍历所有表统计实际行数，比手动逐表查询效率高） #

SET @target_db = '替换为你的数据库名称';
SET @query_sql = '';

-- 拼接所有表的count查询语句
SELECT GROUP_CONCAT(
  'SELECT "', TABLE_NAME, '" AS table_name, COUNT(*) AS actual_rows FROM `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` HAVING actual_rows > 0'
  SEPARATOR ' UNION ALL '
) INTO @query_sql
FROM information_schema.TABLES
WHERE 
  TABLE_SCHEMA = @target_db
  AND TABLE_TYPE = 'BASE TABLE';

-- 执行拼接好的查询
PREPARE exec_stmt FROM @query_sql;
EXECUTE exec_stmt;
DEALLOCATE PREPARE exec_stmt;

PostgreSQL #

快速估算版本 #

SELECT
  n.nspname AS schema_name,
  c.relname AS table_name,
  c.reltuples::BIGINT AS approximate_rows
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
  c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND c.reltuples > 0;

精确统计版本 #

WITH all_tables AS (
  SELECT schemaname || '.' || tablename AS full_table_name
  FROM pg_tables
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
)
SELECT
  full_table_name,
  (xpath('/row/cnt/text()', query_to_xml(FORMAT('SELECT COUNT(*) AS cnt FROM %s', full_table_name), FALSE, TRUE, '')))[1]::TEXT::BIGINT AS actual_rows
FROM all_tables
HAVING actual_rows > 0;

SQL Server #

SELECT
  s.name AS schema_name,
  t.name AS table_name,
  p.rows AS row_count
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.partitions p ON t.object_id = p.object_id
WHERE
  p.index_id IN (0, 1)
  AND p.rows > 0
ORDER BY schema_name, table_name;

注意事项 #

• 所有快速估算版本均从系统元数据表读取统计值，执行速度极快，适合数百张表的场景快速筛选
• 精确统计版本需要逐表扫描计数，性能低于估算版本，但远高于手动逐张写SELECT *查询的效率
• 执行前请替换语句中对应的数据库名、schema过滤规则适配你的实际业务环境

内容的提问来源于stack exchange，提问作者325