SELECT a.table_name
, c.comments
, a.column_name
, b.comments
, a.data_type
, a.data_length, a.column_id
, listagg(to_char(d.position)) within group(order by d.position) as position
FROM user_tab_cols a
LEFT JOIN user_col_comments b
ON a.table_name = b.table_name
AND a.column_name = b.column_name
LEFT JOIN user_tab_comments c
ON c.table_name = b.table_name
LEFT JOIN
(SELECT ucc.table_name
, ucc.column_name
, ucc.position
FROM user_cons_columns ucc
LEFT JOIN user_constraints uc
ON ucc.constraint_name = uc.constraint_name
AND uc.constraint_type = UPPER('p')
) d
ON d.table_name = b.table_name
AND d.column_name = b.column_name
where a.TABLE_NAME like 'TB%'
group by a.table_name
, c.comments
, a.column_name
, b.comments
, a.data_type
, a.data_length, a.column_id
ORDER BY a.table_name,
a.column_id asc ;
上述语句主要运用了listagg函数进行了重复的行合并处理,因为主键的行会重复,把position进行了合并。文章来源地址https://www.toymoban.com/news/detail-682839.html
文章来源:https://www.toymoban.com/news/detail-682839.html
到了这里,关于oracle导出表结构语句(包含注释加主键)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!