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

在实际使用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