关闭

SQLSERVER获取排除了text类型的所有字段名以逗号隔开

136人阅读 评论(0) 收藏 举报
分类:
在实际使用Sqoop抽取表到Hive表,为了方便就使用SQL语句将SQLSERVER字段转换成对应Hive表字段

1、查询表字段和类型(排除了大字段)

select [column_name]=stuff((select ','+[column_name] from information_schema.columns c2 where c2.table_name=c1.table_name and c2.data_type not like '%text%' for xml path('')), 1, 1, '')  
from information_schema.columns  c1
where table_name = 'Contract' and data_type not like '%text%'
group by table_name  




2、去掉特殊符号如 回车、空格、制表符号(排除了大字段)
select [column_name]=stuff((select  
      case when ([CHARACTER_MAXIMUM_LENGTH] is not null or [data_type] = 'uniqueidentifier') then
                ',Replace(Replace(REPLACE('+[column_name]+', CHAR(10), ''''),CHAR(13),''''),CHAR(9),'''') as ' + [column_name]
           else
                ','+[column_name]
      end
  from information_schema.columns c2 where c2.table_name=c1.table_name  and c2.data_type not like '%text%' for xml path('')), 1, 1, '')  
from information_schema.columns  c1
where table_name = 'Contract' and data_type not like '%text%'
group by table_name  




3、去掉特殊符号如 回车、空格、制表符号(含大字段)
select [column_name]=stuff((select  
      case when ([CHARACTER_MAXIMUM_LENGTH] is not null or [data_type] = 'uniqueidentifier') then
                case when [data_type]  like '%text%' then
                        ',Replace(Replace(REPLACE(CAST('+[column_name]+' AS varchar(8000)), CHAR(10), ''''),CHAR(13),''''),CHAR(9),'''') as ' + [column_name]
                     else
                        ',Replace(Replace(REPLACE('+[column_name]+', CHAR(10), ''''),CHAR(13),''''),CHAR(9),'''') as ' + [column_name]
                end
           else
                ','+[column_name]
      end
  from information_schema.columns c2 where c2.table_name=c1.table_name for xml path('')), 1, 1, '')  
from information_schema.columns  c1
where table_name = 'Custom_Captions'
group by table_name  


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:18923次
    • 积分:1491
    • 等级:
    • 排名:千里之外
    • 原创:131篇
    • 转载:26篇
    • 译文:1篇
    • 评论:0条
    文章分类