Oracle数据库查询表信息/列信息(列ID/列名/数据类型/长度/精度/是否可以为null/默认值/是否自增/是否是主键/列描述)

时间：2021-08-16 21:49:42

查询表信息（表名/表描述）

Select table_Name As Name,Comments As Value From User_Tab_Comments Where table_Type='TABLE' Order By table_Name Asc

查询列信息（列ID/列名/数据类型/长度/精度/是否可以为null/默认值/是否自增/是否是主键/列描述）

select a.COLUMN_ID As Colorder,a.COLUMN_NAME As ColumnName,a.DATA_TYPE As TypeName,b.comments As DeText,(Case When a.DATA_TYPE='NUMBER' Then a.DATA_PRECISION Else a.DATA_LENGTH End )As Length,a.DATA_SCALE As Scale,

(Case When (select Count(1)  from user_cons_columns aa, user_constraints bb where aa.constraint_name = bb.constraint_name

 and bb.constraint_type = 'P' and aa.table_name = '表名' And aa.column_name=a.COLUMN_NAME)>0 Then 1 Else 0 End

 ) As IsPK,(

         case when (select count(1) from user_triggers tri INNER JOIN user_source src on tri.trigger_Name=src.Name

            where (triggering_Event='INSERT' and table_name='表名')

        and regexp_like(text, concat(concat('nextval\s+into\s*?:\s*?new\s*?\.\s*?',a.COLUMN_NAME),'\s+?'),'i'))>0

        then 1 else 0 end

) As IsIdentity,

    Case a.NULLABLE  When 'Y' Then 1 Else 0 End As CanNull,

    a.data_default As DefaultVal from user_tab_columns a Inner Join user_col_comments b On a.TABLE_NAME=b.table_name

Where b.COLUMN_NAME= a.COLUMN_NAME   and a.Table_Name='表名'  order by a.column_ID Asc

　　　　　　注：查询包含了 判断列是否自增，查询相对比较慢。Oracle中暂时没有太好的方法 查询列是否自增，这里使用了Oracle的正则，如果有更好方法，欢迎 留言告知，相互学习！！

相关文章