数据库导出表结构查询SQL

数据库导出表结构查询SQL

1、MySQL 数据库

-- mysql
select
	COLUMN_NAME 列名,
	COLUMN_TYPE 数据类型,
	DATA_TYPE 字段类型,
	CHARACTER_MAXIMUM_LENGTH 长度,
	IS_NULLABLE 是否为空,
	COLUMN_DEFAULT 默认值,
	COLUMN_COMMENT 备注
from
	INFORMATION_SCHEMA.COLUMNS
where
	table_schema = 'db' -- 数据库名称
	and table_name = 'TB_NAME' -- 表名

2、DM 达梦数据库

-- DM 达梦
	SELECT
	t.COLUMN_NAME AS 列名,
	c.COMMENTS AS 中文描述, 
	CONCAT(t.DATA_TYPE, '(', t.DATA_LENGTH, ')') AS 数据类型,
	t.DATA_TYPE AS 字段类型,
	CASE t.NULLABLE WHEN 'N' THEN '否' ELSE '是'END AS 是否为空, 
	t.DATA_DEFAULT AS 默认值
FROM
	user_tab_columns t
LEFT JOIN user_col_comments c
 ON
	t.table_name = c.table_name
	AND t.COLUMN_NAME = c.COLUMN_NAME
WHERE
	t.table_name = 'TB_NAME'  -- 表名
GROUP BY
	t.COLUMN_NAME

3、Oracle 数据库

	
-- oracle
	SELECT
    t.COLUMN_NAME 列名,
    t.DATA_TYPE 数据类型,
    t.DATA_LENGTH 长度,
    t.NULLABLE 是否为空,
    c.comments 注释

FROM
    user_tab_columns t
INNER JOIN user_col_comments c 
ON 
    t.TABLE_NAME = c.table_name
    AND t.COLUMN_NAME = c.column_name
WHERE
    t.Table_Name = 'TB_NAME' -- 表名
ORDER BY
    t.column_name