1、首先拼接查询语句
-- 在查询出来的结果后面加上 ' null end as has_special_character '
select
case when data_type like '%text%' then
' case when CAST('+column_name+' AS varchar(8000)) like ''%''+CHAR(10)+''%'' or CAST('+[column_name]+' AS varchar(8000)) like ''%''+CHAR(9)+''%'' or CAST('+[column_name]+' AS varchar(8000)) like ''%''+CHAR(13)+''%'' then '''+column_name+''' else '
else
' case when '+column_name+' like ''%''+CHAR(10)+''%'' or CAST('+[column_name]+' AS varchar(8000)) like ''%''+CHAR(9)+''%'' or CAST('+[column_name]+' AS varchar(8000)) like ''%''+CHAR(13)+''%'' then '''+column_name+''' else '
end 'qu'
-- column_name + ',' as colname
from information_schema.columns
where table_name = 'MChannel'
and ((CHARACTER_MAXIMUM_LENGTH is not null or data_type = 'uniqueidentifier') or data_type like '%text%' )
2、由于SQLSERVER只能嵌套10个case when 语句,所以步骤1查询出来的脚本要进行分拆,如下:
1)步骤一的查询结果:
case when chan_Name like '%'+CHAR(10)+'%' or CAST(chan_Name AS varchar(8000)) like '%'+CHAR(9)+'%' or CAST(chan_Name AS varchar(8000)) like '%'+CHAR(13)+'%' then 'chan_Name' else
case when chan_Status like '%'+CHAR(10)+'%' or CAST(chan_Status AS varchar(8000)) like '%'+CHAR(9)+'%' or CAST(chan_Status AS varchar(8000)) like '%'+CHAR(13)+'%' then 'chan_Status' else
case when chan_reference like '%'+CHAR(10)+'%' or CAST(chan_reference AS varchar(8000)) like '%'+CHAR(9)+'%' or CAST(chan_reference AS varchar(8000)) like '%'+CHAR(13)+'%' then 'chan_reference' else
case when chan_type like '%'+CHAR(10)+'%' or CAST(chan_type AS varchar(8000)) like '%'+CHAR(9)+'%' or CAST(chan_type AS varchar(8000)) like '%'+CHAR(13)+'%' then 'chan_type' else
case when chan_purpose like '%'+CHAR(10)+'%' or CAST(chan_purpose AS varchar(8000)) like '%'+CHAR(9)+'%' or CAST(chan_purpose AS varchar(8000)) like '%'+CHAR(13)+'%' then 'chan_purpose' else
case when CAST(chan_scheme AS varchar(8000)) like '%'+CHAR(10)+'%' or CAST(chan_scheme AS varchar(8000)) like '%'+CHAR(9)+'%' or CAST(chan_scheme AS varchar(8000)) like '%'+CHAR(13)+'%' then 'chan_scheme' else
case when CAST(chan_competitor AS varchar(8000)) like '%'+CHAR(10)+'%' or CAST(chan_competitor AS varchar(8000)) like '%'+CHAR(9)+'%' or CAST(chan_competitor AS varchar(8000)) like '%'+CHAR(13)+'%' then 'chan_competitor' else
case when chan_contractstatus like &#