Oracle,PostgreSQL,MySql,SqlServer各数据库查元信息的SQL

Oracle

查询表字段信息

SELECT
    a.COLUMN_NAME AS B_NAME,  -- 字段名称
    a.DATA_TYPE,              -- 字段数据类型
    CASE 
        WHEN a.COLUMN_NAME IN (
            SELECT cols.column_name
            FROM all_constraints cons, all_cons_columns cols
            WHERE cons.constraint_type = 'P'  -- 主键约束
              AND cons.constraint_name = cols.constraint_name
              AND cons.owner = cols.owner
              AND cols.COLUMN_NAME = a.COLUMN_NAME
              AND cols.TABLE_NAME = 'TB_CIS_CONSULT_DETAIL'
              AND cons.OWNER = 'GZFY'
        ) THEN 'PRI'           -- 如果字段是主键，则标记为 'PRI'
        ELSE NULL              -- 否则为 NULL
    END COLUMN_KEY,
    b.COMMENTS AS remark       -- 字段备注
FROM all_tab_cols a 
LEFT JOIN all_col_comments b
    ON a.TABLE_NAME = b.TABLE_NAME 
    AND a.COLUMN_NAME = b.COLUMN_NAME 
    AND a.OWNER = b.OWNER
WHERE a.TABLE_NAME = 'TB_CIS_CONSULT_DETAIL'  -- 目标表名称
  AND HIDDEN_COLUMN = 'NO'                    -- 排除隐藏字段
  AND a.OWNER = 'GZFY';                       -- 表的所有者

查询表的键字段信息

SELECT
    column_name -- 键字段的名称
FROM all_ind_columns
WHERE table_owner = 'GZFY'   -- 表的所有者
  AND table_name = 'TB_CIS_CONSULT_DETAIL';  -- 目标表名称

查询表名或视图名

SELECT DISTINCT
    view_name AS table_name  -- 视图名称
FROM all_views
WHERE OWNER = 'GZFY'         -- 所有者
UNION
SELECT DISTINCT
    table_name