查询数据库相关的表信息

最新推荐文章于 2022-09-28 17:10:09 发布

cn_hdz

最新推荐文章于 2022-09-28 17:10:09 发布

阅读量354

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/java_HDZ/article/details/81979873

版权

sql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1.查询该数据库中所有的表名和表注解

 -- oracle
    SELECT TABLE_NAME,COMMENTS FROM USER_TAB_COMMENTS

 -- mysql
SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema= '数据库名称'

2.查询当前表结构信息

-- oracle
 SELECT T1.COLUMN_ID, T2.COMMENTS,T1.COLUMN_NAME,T1.DATA_TYPE ||  '(' || T1.DATA_LENGTH || ')' as DATA_TYPE FROM USER_TAB_COLS T1, USER_COL_COMMENTS T2 WHERE T1.TABLE_NAME = T2.TABLE_NAME AND T1.COLUMN_NAME = T2.COLUMN_NAME AND T1.TABLE_NAME = '表名' ORDER BY COLUMN_ID ASC

--mysql
select ORDINAL_POSITION as COLUMN_ID,COLUMN_COMMENT as COMMENTS, Column_Name as COLUMN_NAME, CONCAT( CONCAT( CONCAT(data_type,'('), (case when data_type = 'float' or data_type = 'double' or data_type = 'decimal' then  CONCAT( NUMERIC_PRECISION,CONCAT( ',' , NUMERIC_SCALE ) )  when  data_type = 'int' then NUMERIC_PRECISION else CHARACTER_MAXIMUM_LENGTH end )  ) ,')')  as DATA_TYPE from information_schema.columns where table_schema = '数据库名' and table_name = '表名' order by ORDINAL_POSITION asc