欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-03-21 21:00:10
...
               

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