SQL 查詢表字段信息

一、MySQL

（一）查詢所有表名

查詢指定數(shù)據(jù)庫(kù)下表名

select table_name,table_comment from information_schema.tables where table_schema='grandly_forum_db'

（二）查詢表下所有字段

查詢指定數(shù)據(jù)庫(kù)下表的字段信息

select 
a.table_schema,
a.table_name,
b.table_comment 表說(shuō)明,
a.COLUMN_NAME 字段名,
a.column_comment 字段說(shuō)明,
a.column_type 字段類型,
a.column_key 約束 from  information_schema.columns a
join information_schema.TABLES b on a.table_name = b.TABLE_NAME
where a.table_schema = '數(shù)據(jù)庫(kù)名'
and a.table_name = '表名';

二、Oracle

（一）查詢表下所有字段

SELECT
DISTINCT
c.owner 用戶,
c.table_name 英文表名 ,
t.comments 中文表名稱,
cc.column_name 字段名,
cc.comments 字段注釋 ,
c.data_type || '(' || c.data_length || ')' 字段類型及長(zhǎng)度
from
dba_tab_columns c
join dba_col_comments cc on c.table_name = cc.table_name
and c.column_name = cc.column_name
join dba_tab_comments t on c.table_name = t.table_name
where 
c.owner = 'YZZX'
-- AND t.comments like '%船舶信息%'
AND c.table_name =  'SHX_SHIPCERT'
order BY
cc.column_name 

（二）查詢庫(kù)下表字段信息

with t1 as(
SELECT
 t.OWNER owner_name,
 t.table_name,
 t.column_name,
 f.comments,
 t.data_type,
 t.data_length
FROM all_tab_columns t 
JOIN all_col_comments f ON t.table_name = f.table_name AND t.column_name = f.column_name
ORDER BY 
t.table_name,
t.column_name
),
t2 as (
select col.column_name pk,col.table_name
from user_constraints con,user_cons_columns col
where
con.constraint_name=col.constraint_name and con.constraint_type='P'
)

select 
distinct 
t1.owner_name 用戶名,
t1.table_name 表名,
t2.pk 主鍵,
t1.column_name 字段名,
t1.comments 字段注釋,
t1.data_type 數(shù)據(jù)類型,
t1.data_length 長(zhǎng)度
from t1
join t2 on t1.table_name = t2.table_name
where t1.owner_name = 'ZHZFHZK' and t1.comments is not null
order by t1.table_name,column_name

（三）查詢用戶下表的外建等信息

 select distinct 
 c.owner 用戶名,
 CONSTRAINT_TYPE 鍵類型,
 c.TABLE_NAME 子表名,
 c.CONSTRAINT_NAME 子表鍵名稱,
 d.TABLE_NAME 父表名稱,
 c.R_CONSTRAINT_NAME 父表鍵名稱,
 d.COLUMN_NAME 字段名稱
 from all_constraints c 
  join all_cons_columns d on c.constraint_type = 'R' and c.R_CONSTRAINT_NAME = d.CONSTRAINT_NAME
 where c.owner = 'SCOTT' ;

（四）oracle查詢用戶下表外建信息

SELECT
    t1.table_name AS table_name,
    t2.table_name AS f_table_name,
    t1.column_name,
    t2.column_name as f_column_name
FROM
    all_constraints cons
    JOIN all_cons_columns col1 ON cons.owner = col1.owner AND cons.constraint_name = col1.constraint_name
    JOIN all_tab_columns t1 ON cons.owner = t1.owner AND col1.table_name = t1.table_name AND col1.column_name = t1.column_name
    JOIN all_cons_columns col2 ON cons.owner = col2.owner AND cons.r_owner = col2.owner AND cons.r_constraint_name = col2.constraint_name
    JOIN all_tab_columns t2 ON cons.r_owner = t2.owner AND col2.table_name = t2.table_name AND col2.column_name = t2.column_name
WHERE
   cons.constraint_type = 'R' AND t1.owner = 'WANGLIN'
ORDER BY t1.table_name;

關(guān)注林哥，持續(xù)更新哦?。?！★,°:.☆(￣▽￣)/$:.°★ 。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-652103.html

到了這里，關(guān)于SQL 查詢表字段信息的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！