SQL 知识点积累

最新推荐文章于 2023-10-06 17:37:27 发布

麦子的魔术

最新推荐文章于 2023-10-06 17:37:27 发布

阅读量112

点赞数

分类专栏：关系型数据库

本文链接：https://blog.csdn.net/weixin_41689725/article/details/105436401

版权

关系型数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

SQL 知识点积累

1、oracle 、msyql、达梦数据字段获取sql
oracle：

select case when a.owner is null then '' else a.owner end,case when a.table_name is null then '' else a.table_name end,case when a.column_name is null then '' else lower(a.column_name) end,case when b.COMMENTS is null then '' else  REPLACE(REPLACE(b.COMMENTS, CHAR(10), '\\u200b'), CHAR(13),'\\u200c') end,to_char(column_id,'99999'),case when a.DATA_TYPE is null then '' else a.DATA_TYPE end,to_char(NVL(DATA_LENGTH,'0'),'99999'),to_char(NVL(a.DATA_PRECISION,'0'),'99999'),to_char(NVL(a.DATA_SCALE,'0'),'99999')  from all_tab_columns a , all_col_comments b  where a.table_name = b.table_name and a.column_name = b.column_name  and a.Table_Name=upper('{tName}')  and a.owner = upper('{own}') order by  a.column_id

msyql

select case when table_schema is null then '' else table_schema end,case when TABLE_NAME is null then '' else TABLE_NAME end,case when upper(column_name) is null then '' else lower(column_name) end,case when COLUMN_COMMENT is null then '' else REPLACE(REPLACE(COLUMN_COMMENT, CHAR(10), '\\u200b'), CHAR(13),'\\u200c') end,case when ordinal_position is null then '' else ordinal_position end ,case when DATA_TYPE is null then '' else DATA_TYPE end,case when NUMERIC_PRECISION is null then '' else NUMERIC_PRECISION end,case when NUMERIC_SCALE is null then '' else NUMERIC_SCALE end from information_schema.columns  where table_name=upper('{tName}')  and table_schema = upper('{own}') order by  ordinal_position

达梦

select case when a.owner is null then '' else a.owner end,case when a.table_name is null then '' else a.table_name end,case when a.column_name is null then '' else lower(a.column_name) end,case when b.COMMENTS is null then '' else REPLACE(REPLACE(b.COMMENTS, CHAR(10), '\\u200b'), CHAR(13),'\\u200c') end,to_char(column_id,'99999'),case when a.DATA_TYPE is null then '' else a.DATA_TYPE end,to_char(NVL(DATA_LENGTH,'0'),'99999'),to_char(NVL(a.DATA_PRECISION,'0'),'99999'),to_char(NVL(a.DATA_SCALE,'0'),'99999')  from all_tab_columns a , all_col_comments b  where a.table_name = b.table_name and a.column_name = b.column_name  and a.Table_Name=upper('{tName}')  and a.owner = upper('{own}') order by  a.column_id