mysql、oracle、sqlserver根据对应的表查询表中的所有字段名称、类型、别名、长度等信息

上一节我们介绍了如何查询数据库中的所有表，当我们把表查询出来之后，我们如何根据对应的表查询表中的所有字段名称、类型、别名、长度等信息呢?我不是一个喜欢讲废话的人，直接上代码吧

1.mysql

SELECT
 t.COLUMN_NAME AS NAME,
 (
  CASE
  WHEN t.IS_NULLABLE = 'YES' THEN
   '1'
  ELSE
   '0'
  END
 ) AS isNull,
 (t.ORDINAL_POSITION * 10) AS sort,
 t.COLUMN_COMMENT AS comments,
 t.COLUMN_TYPE AS jdbcType
FROM
 information_schema.`COLUMNS` t
WHERE
 t.TABLE_SCHEMA = (SELECT DATABASE())
AND t.TABLE_NAME = 'db_movie'   - - 此处传入表明
ORDER BY
 t.ORDINAL_POSITION

2.sqlServer

SELECT
 t.COLUMN_NAME AS NAME,
 (
  CASE
  WHEN t.IS_NULLABLE = 'YES' THEN
   '1'
  ELSE
   '0'
  END
 ) AS isNull,
 (t.ORDINAL_POSITION * 10) AS sort,
 isnull(g.[ VALUE ], '') AS comments,
 (
  t.DATA_TYPE + CASE
  WHEN t.DATA_TYPE IN (
   'varchar',
   'char',
   'nvarchar',
   'nchar'
  ) THEN
   '(' + CONVERT (
    VARCHAR,
    t.CHARACTER_MAXIMUM_LENGTH
   ) + ')'
  WHEN t.DATA_TYPE IN ('numeric', 'decimal') THEN
   '(' + CONVERT (
    VARCHAR,
    t.NUMERIC_PRECISION_RADIX
   ) + ',' + CONVERT (
    VARCHAR,
    ISNULL(t.NUMERIC_SCALE, 0)
   ) + ')'
  ELSE
   ''
  END
 ) AS jdbcType
FROM
 INFORMATION_SCHEMA. COLUMNS t
INNER JOIN sys.sysobjects o ON t.TABLE_NAME = o. NAME
AND SCHEMA_NAME (o.uid) = t.TABLE_SCHEMA
LEFT JOIN sys.extended_properties g ON o.id = g.major_id
AND t.ORDINAL_POSITION = g.minor_id
AND g. NAME = 'MS_Description'
WHERE
 t.TABLE_SCHEMA = (SCHEMA_NAME())
AND t.TABLE_NAME  = 'db_movie'   - - 此处传入表明

 ORDER BY
  t.ORDINAL_POSITION

3.Oracle

SELECT
 t.COLUMN_NAME AS NAME  ,(
  CASE
  WHEN t.NULLABLE = 'Y' THEN
   '1'
  ELSE
   '0'
  END
 ) AS isNull,
 (t.COLUMN_ID * 10) AS sort,
 c.COMMENTS AS comments,
 decode(
  t.DATA_TYPE,
  'DATE',
  t.DATA_TYPE || '(' || t.DATA_LENGTH || ')',
  'VARCHAR2',
  t.DATA_TYPE || '(' || t.DATA_LENGTH || ')',
  'VARCHAR',
  t.DATA_TYPE || '(' || t.DATA_LENGTH || ')',
  'NVARCHAR2',
  t.DATA_TYPE || '(' || t.DATA_LENGTH / 2 || ')',
  'CHAR',
  t.DATA_TYPE || '(' || t.DATA_LENGTH || ')',
  'NUMBER',
  t.DATA_TYPE || (
   nvl2 (
    t.DATA_PRECISION,
    nvl2 (
     decode(
      t.DATA_SCALE,
      0,
      NULL,
      t.DATA_SCALE
     ),
     '(' || t.DATA_PRECISION || ',' || t.DATA_SCALE || ')',
     '(' || t.DATA_PRECISION || ')'
    ),
    '(18)'
   )
  ),
  t.DATA_TYPE
 ) AS jdbcType
FROM
 user_tab_columns t,
 user_col_comments c
WHERE
 t.TABLE_NAME = c.table_name
AND t.COLUMN_NAME = c.column_name 
AND t.TABLE_NAME = upper('db_movie'  
 )
 ORDER BY
  t.COLUMN_ID