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               -- 表名称
FROM all_tables
WHERE OWNER = 'GZFY';        -- 所有者
ORDER BY table_name;         -- 按名称排序

预览表数据

SELECT 
    "TB_CIS_EMR_FEE"."UPDATE_DATE", 
    "TB_CIS_EMR_FEE"."COMMENTS", 
    "TB_CIS_EMR_FEE"."ID",
    -- 其他字段...
FROM "GZFY"."TB_CIS_EMR_FEE"
WHERE ROWNUM <= 10;          -- 限制返回结果为10行

---

PostgreSQL

查询表字段信息

SELECT 
    col.COLUMN_NAME,            -- 字段名称
    col.data_type,              -- 数据类型
    CASE 
        WHEN col.COLUMN_NAME IN (
            SELECT conname 
            FROM pg_catalog.pg_constraint
            JOIN pg_catalog.pg_namespace
              ON pg_catalog.pg_constraint.connamespace = pg_catalog.pg_namespace.oid
            JOIN pg_catalog.pg_class
              ON pg_catalog.pg_constraint.conrelid = pg_catalog.pg_class.oid
            WHERE pg_catalog.pg_constraint.contype = 'p' -- 主键约束
              AND pg_catalog.pg_namespace.nspname = 'public'
              AND pg_catalog.pg_class.relname = 'salaries'
        ) THEN 'PRI'
        ELSE NULL
    END PRI,                    -- 标记是否为主键
    (
        SELECT descr.description
        FROM pg_class AS cls
        INNER JOIN pg_attribute AS attr ON cls.oid = attr.attrelid
        LEFT JOIN pg_description AS descr ON (descr.objoid = cls.oid AND descr.objsubid = attr.attnum)
        WHERE cls.relkind IN ('r', 'v') 
          AND cls.relname = 'salaries' 
          AND attr.attname = col.COLUMN_NAME
    ) AS DESCRIPTION             -- 字段描述
FROM information_schema.COLUMNS col
WHERE table_schema = 'public'
  AND table_catalog = 'employees'
  AND table_name = 'salaries';

预览表数据

SELECT 
    "salaries"."emp_no", 
    "salaries"."salary", 
    to_char("salaries"."from_date", 'YYYY-MM-DD HH24:MI:SS'), 
    to_char("salaries"."to_date", 'YYYY-MM-DD HH24:MI:SS')
FROM "employees"."public"."salaries"
LIMIT 10; -- 限制返回10行

查询表名或视图名

SELECT table_name
FROM information_schema.tables
WHERE table_catalog = 'employees'
  AND table_schema = 'public'
  AND table_type NOT IN ('FOREIGN TABLE'); -- 排除外部表

---

MySQL

查询表名或视图名

SELECT DISTINCT TABLE_NAME, 
       TABLE_TYPE != 'BASE TABLE' AS IS_VIEW -- 判断是否为视图
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "test"; -- 目标数据库

查询表字段信息

SELECT 
    COLUMN_NAME,          -- 字段名称
    DATA_TYPE,            -- 数据类型
    COLUMN_KEY,           -- 键类型
    COLUMN_COMMENT        -- 字段备注
FROM information_schema.columns
WHERE TABLE_SCHEMA = 'test' 
  AND TABLE_NAME = 'tb_cis_patient_info';

查询表的键字段信息

SELECT DISTINCT 
    column_name -- 键字段的名称
FROM information_schema.STATISTICS
WHERE table_schema = 'test'
  AND table_name = 'tb_cis_patient_info';

预览表数据

SELECT 
    card_number, 
    card_type, 
    medical_institut_code,
    -- 其他字段...
FROM test.tb_cis_patient_info
LIMIT 10; -- 限制返回10行

---

SQL Server

查询表字段信息

SELECT
    remarks.column_name,      -- 字段名称
    columns.DATA_TYPE,        -- 数据类型
    columns.PRI,              -- 键标记
    remarks.remark            -- 字段备注
FROM (
    SELECT
        sc.name AS column_name,
        sep.value AS remark -- 字段备注
    FROM sys.tables st
    INNER JOIN sys.columns sc ON st.object_id = sc.object_id
    LEFT JOIN sys.extended_properties sep ON st.object_id = sep.major_id
       AND sc.column_id = sep.minor_id
       AND sep.name = 'MS_Description'
    WHERE st.name = 'CB_COST_ITEM' -- 表名称
) remarks
LEFT JOIN (
    SELECT DISTINCT
        T1.COLUMN_NAME,
        T1.DATA_TYPE,
        T2.PRI
    FROM (
        SELECT
            COLUMN_NAME,
            DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'CB_COST_ITEM'
    ) T1
    LEFT JOIN (
        SELECT
            COLUMN_NAME,
            'PRI' AS PRI
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE TABLE_NAME = 'CB_COST_ITEM' AND CONSTRAINT_NAME LIKE 'PK%'
    ) T2 ON T2.COLUMN_NAME = T1.COLUMN_NAME
) columns ON columns.COLUMN_NAME = remarks.column_name;

查询表的键字段信息

SELECT
    col.name AS ColumnName -- 键字段的名称
FROM sys.indexes ind
INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id
INNER JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
INNER JOIN sys.tables t ON ind.object_id = t.object_id
WHERE ind.is_primary_key = 0
  AND ind.is_unique = 0
  AND ind.is_unique_constraint = 0
  AND t.is_ms_shipped = 0
  AND t.name = 'CB_COST_ITEM'; -- 表名称

查询表名或视图名

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_NAME; -- 按表名排序

预览表数据

SELECT TOP 10 
    [PK_ID], 
    [ITEM_CODE], 
    [ITEM_NAME],
    -- 其他字段...
FROM [medicare_ZhuHaiJinWan].[dbo].[CB_COST_ITEM];