概述:
通过Navicat工具或者PLSQL工具查询Oracle数据库,MySQL数据库,sqlserver数据库的表结构信息,并导出excel格式的或者CSV格式的文件。
Oracle数据库:
SELECT
t.table_name 表名,
-- t.column_id 序列号,
t.column_name 字段名,
t.data_type 字段类型,
t.data_length 字段长度,
-- t.nullable 可否为空,
-- t.data_default 默认值,
c.comments 字段注释
FROM
dba_tab_cols t,
dba_col_comments c
WHERE
t.owner='ACDM'
-- upper( t.table_name ) = '表名'
AND c.table_name = t.table_name
AND c.column_name = t.column_name
AND t.hidden_column = 'NO'
ORDER BY 1
MySQL数据库:
select
TABLE_NAME 表名字,
COLUMN_NAME 列名,
DATA_TYPE 字段类型,
CHARACTER_MAXIMUM_LENGTH 长度,
IS_NULLABLE 是否为空,
IF(column_key = 'PRI','Y','') 是否为主键,
COLUMN_DEFAULT 默认值,
COLUMN_COMMENT 备注
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'testdb'
AND table_name = '表名';
sqlserver数据库:
SELECT
A.name AS table_name,
B.name AS column_name,
B.max_length ,
C.value AS column_description
FROM sys.tables A
INNER JOIN sys.columns B ON B.object_id = A.object_id
LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id
WHERE A.name = 'Sys_User'