获取通过sql查询数据库的表，字段，主键，自增，字段类型等信息

               

1.查询所有表，以及表的备注信息。

Oracle数据库

 

select t.table_name tableName, cmts.comments descr from user_tables tleft join user_tab_comments cmts on t.table_name = cmts.table_namewhere t.table_name like '%'

SQL Server

 

 

Select d.Name tableName, isnull(e.value,'') descr From SysObjects dleft join  sys.extended_properties  e on d.id = e.major_id   and   e.minor_id=0 Where d.XType='U' and d.name like ? order By d.Name

 

 

MySQL

 

SELECT table_name tableName, TABLE_COMMENT descr  FROM information_schema.tables WHERE table_schema = ? and table_name like ? ORDER BY table_name DESC

2.查询指定表的 字段名称，是否主键，是否自增，数据类型，字段注释信息。

 

Oracle

 

select c.column_name columnName, case when cu.column_name is null then 'false' else 'true' end as pkColumn,'false' as  autoAdd , c.data_type jdbcType , cmts.comments descrfrom user_tab_columns  cleft 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_nameleft join user_col_comments cmts on cmts.table_name = c.table_name and cmts.column_name = c.column_name where c.table_name = UPPER(?)

SQL Server

 

 

SELECT t1.name columnName,case when  t4.id is null then 'false' else 'true' end as pkColumn,  case when  COLUMNPROPERTY( t1.id,t1.name,'IsIdentity') = 1 then 'true' else 'false' end as  autoAdd ,t5.name jdbcType  ,cast(isnull(t6.value,'') as varchar(2000)) descr    ,tb.name as tableName FROM SYSCOLUMNS t1left join SYSOBJECTS t2 on  t2.parent_obj = t1.id  AND t2.xtype = 'PK' left join SYSINDEXES t3 on  t3.id = t1.id  and t2.name = t3.name  left join SYSINDEXKEYS t4 on t1.colid = t4.colid and t4.id = t1.id and t4.indid = t3.indidleft join systypes  t5 on  t1.xtype=t5.xtypeleft join sys.extended_properties t6   on  t1.id=t6.major_id   and   t1.colid=t6.minor_idleft join SYSOBJECTS tb  on  tb.id=t1.idwhere tb.name=?

MySQL

 

 

SELECT a.column_Name AS columnName  ,CASE WHEN p.column_Name IS NULL THEN 'false' ELSE 'true' END  AS pkColumn ,CASE WHEN a.extra = 'auto_increment' THEN 'true' ELSE 'false' END  AS autoAdd,a.data_type jdbcType, column_COMMENT descr  FROM information_schema.COLUMNS  aLEFT 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  

 

 

 

 

 

 

 

           

再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！https://blog.csdn.net/jiangjunshow