sql查询所有表字段、类型、是否主键

2026-05-14
达梦

-- 达梦
SELECT
  c.COLUMN_NAME,
  c.DATA_TYPE,
CASE

    WHEN k.COLUMN_NAME IS NOT NULL THEN
    '1' ELSE '0' 
  END AS IS_PKEY 
FROM
  USER_TAB_COLUMNS c
  LEFT JOIN (
    SELECT
      cols.TABLE_NAME,
      cols.COLUMN_NAME 
    FROM
      ALL_CONSTRAINTS cons
      JOIN ALL_CONS_COLUMNS cols ON cons.CONSTRAINT_NAME = cols.CONSTRAINT_NAME 
      AND cons.TABLE_NAME = cols.TABLE_NAME 
    WHERE
      cons.CONSTRAINT_TYPE = 'P' 
      AND cons.TABLE_NAME = 'yg_datacenter_xsssxx' -- 替换为实际表名

  ) k ON c.TABLE_NAME = k.TABLE_NAME 
  AND c.COLUMN_NAME = k.COLUMN_NAME 
WHERE
  c.TABLE_NAME = 'yg_datacenter_xsssxx' -- 替换为实际表名

ORDER BY
  c.COLUMN_ID;