上一节我们介绍了如何查询数据库中的所有表,当我们把表查询出来之后,我们如何根据对应的表查询表中的所有字段名称、类型、别名、长度等信息呢?我不是一个喜欢讲废话的人,直接上代码吧
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
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' - - 此处传入表明
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
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
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