数据库|获取通过sql查询数据库的表，字段，主键，自增，字段类型等信息。
1.查询所有表，以及表的备注信息。
Oracle数据库
select t.table_name tableName, cmts.comments descr
from user_tables t
left join user_tab_comments cmts on t.table_name = cmts.table_name
where t.table_name like '%'SQL Server
Select d.Name tableName, isnull(e.value,'') descr
From SysObjects d
left joinsys.extended_propertiese on d.id = e.major_idande.minor_id=0
Where d.XType='U' and d.name like ? order By d.NameMySQL
SELECT table_name tableName, TABLE_COMMENT descr
FROM information_schema.tables
WHERE table_schema = ? and table_name like ? ORDER BY table_name DESC2.查询指定表的 字段名称，是否主键，是否自增，数据类型，字段注释信息。
Oracle
select c.column_name columnName, case when cu.column_name is null then 'false' else 'true' end as pkColumn,'false' asautoAdd
, c.data_type jdbcType , cmts.comments descr
from user_tab_columnsc
left join user_constraints au on c.table_name = au.table_name and au.constraint_type = 'P'
left join user_cons_columns cu on cu.constraint_name = au.constraint_name and c.column_name = cu.column_name
left join user_col_comments cmts on cmts.table_name = c.table_name and cmts.column_name = c.column_name
where c.table_name = UPPER(?)
order by c.column_idSQL Server
SELECT t1.name columnName,case whent4.id is null then 'false' else 'true' end as pkColumn,
case whenCOLUMNPROPERTY( t1.id,t1.name,'IsIdentity') = 1 then 'true' else 'false' end asautoAdd
,t5.name jdbcType
,cast(isnull(t6.value,'') as varchar(2000)) descr
,tb.name as tableName
FROM SYSCOLUMNS t1
left join SYSOBJECTS t2 ont2.parent_obj = t1.idAND t2.xtype = 'PK'
left join SYSINDEXES t3 ont3.id = t1.idand t2.name = t3.name
left join SYSINDEXKEYS t4 on t1.colid = t4.colid and t4.id = t1.id and t4.indid = t3.indid
left join systypest5 ont1.xtype=t5.xtype
left join sys.extended_properties t6ont1.id=t6.major_idandt1.colid=t6.minor_id
left join SYSOBJECTS tbontb.id=t1.id
where tb.name=?MySQL
SELECT a.column_Name AS columnName,CASE WHEN p.column_Name IS NULL THEN 'false' ELSE 'true' ENDAS pkColumn
,CASE WHEN a.extra = 'auto_increment' THEN 'true' ELSE 'false' ENDAS autoAdd,a.data_type jdbcType, column_COMMENT descr
FROM information_schema.COLUMNSa
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS p ON a.table_schema = p.table_schema AND a.table_name = p.TABLE_NAME AND a.COLUMN_NAME = p.COLUMN_NAME AND p.constraint_name='PRIMARY'
WHERE a.table_schema = ? AND a.table_name = ?
ORDER BY a.ordinal_position【数据库|获取通过sql查询数据库的表，字段，主键，自增，字段类型等信息。】
推荐阅读
- Docker应用:容器间通信与Mariadb数据库主从复制
- gitlab|gitlab 通过备份还原 admin/runner 500 Internal Server Error
- 数据库设计与优化
- EditText默认不获取焦点弹出键盘
- 数据库总结语句
- MySql数据库备份与恢复
- whlie循环和for循环的应用
- 数据库|SQL行转列方式优化查询性能实践
- MySQL数据库的基本操作
- 如何通过锻炼的方法治疗前列腺肥大