sql server 2012之，全库精确或者模糊搜索某个字符串的两种算法

ALTER proc [dbo].[P_FIND_CHAR_v1]
    @CFINDSTR nvarchar(max)
  , @I bit = 1 --@I=1精确查找,@I=0 like 查找，默认精确查找
begin
    declare @CSQL nvarchar(max);
    declare @CWHERECHAR nvarchar(max); --精确查找和like查找脚本写法有区别，所以使用此变量
    declare @CTABLE_SCHEMA nvarchar(max)
          , @CTABLE_NAME   nvarchar(max)
          , @CCOLUMN_NAME  nvarchar(max)
          , @ICOUNT        int;
    declare CUR_SEARCH cursor for
    select TABLE_SCHEMA
         , TABLE_NAME
         , COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS
    where upper(DATA_TYPE) like '%CHAR%' --char类型的字段
          --AND TABLE_NAME IN()--还可以限定表的范围
          and TABLE_NAME in
                  select TABLE_NAME
                  from INFORMATION_SCHEMA.TABLES
                  where TABLE_TYPE = 'BASE TABLE'
              ); --只有表，排除视图
    open CUR_SEARCH;
    fetch next from CUR_SEARCH
    into @CTABLE_SCHEMA
       , @CTABLE_NAME
       , @CCOLUMN_NAME;
    while @@fetch_status = 0
    begin
        set @CWHERECHAR = case @I
                              when 1 then
                                  ' =''' + @CFINDSTR + ''''      --精确查找
                              when 0 then
                                  ' LIKE''%' + @CFINDSTR + '%''' --like匹配查找
                          end; --精确查找和like查找的查询条件不一样
        set @CSQL
            = N'SELECT @ICOUNT=COUNT(*) FROM [' + @CTABLE_SCHEMA + N'].[' + @CTABLE_NAME + N'] WHERE [' + @CCOLUMN_NAME
              + N']' + @CWHERECHAR;
        exec sp_executesql @CSQL, N'@ICOUNT INT OUT', @ICOUNT out;
        if @ICOUNT >= 1
        begin
            print 'SELECT ''' + @CTABLE_SCHEMA + '.' + @CTABLE_NAME + ''' AS TABLE_NAME ,' --在生成的查询脚本中加上表架构和表名
                  + '* FROM [' + @CTABLE_SCHEMA + '].[' + @CTABLE_NAME + '] WHERE [' + @CCOLUMN_NAME + ']'
                  + @CWHERECHAR;
        end;
        fetch next from CUR_SEARCH
        into @CTABLE_SCHEMA
           , @CTABLE_NAME
           , @CCOLUMN_NAME;
    end;
    close CUR_SEARCH;
    deallocate CUR_SEARCH;
end;

ALTER procedure [dbo].[P_FIND_CHAR_v2]
    @input_char nvarchar(max)
  , @type bit = 1 --0=模糊匹配，1=精确匹配
--全库精确或者模糊搜索某个字符串，只搜索char类型的列
begin
    declare @CSQL nvarchar(max);
    declare @CWHERECHAR nvarchar(max); --精确查找和like查找脚本写法有区别，所以使用此变量
    declare @CTABLE_SCHEMA nvarchar(max)
          , @CTABLE_NAME   nvarchar(max)
          , @CCOLUMN_NAME  nvarchar(max)
          , @ICOUNT        int;
    declare CUR_SEARCH cursor for
    select d.name                   as schema_name
         , object_name(a.object_id) as table_name
         , a.name                   as column_name
    from sys.columns           as a --列表
        inner join sys.types   as b --类型表
            on a.system_type_id = b.system_type_id
        inner join sys.objects as c --对象表
            on a.object_id = c.object_id
        inner join sys.schemas as d --架构表
            on c.schema_id = d.schema_id
    where c.type = 'U'
          and b.name like '%char%'
          --and a.max_length >= len(@input_char) --如果查询的列的长度少于输入字符的长度，则不予查询，这里有个bug，如果是max的话，长度为-1，不可以。
    order by table_name
           , a.column_id;
    open CUR_SEARCH;
    fetch next from CUR_SEARCH
    into @CTABLE_SCHEMA
       , @CTABLE_NAME
       , @CCOLUMN_NAME
    while @@fetch_status = 0
    begin
        set @CWHERECHAR = case @type
                              when 1 then
                                  ' =''' + @input_char + ''''      --精确查找
                              when 0 then
                                  ' LIKE''%' + @input_char + '%''' --like匹配查找
                          end; --精确查找和like查找的查询条件不一样
        set @CSQL
            = N'SELECT @ICOUNT=COUNT(*) FROM [' + @CTABLE_SCHEMA + N'].[' + @CTABLE_NAME + N'] WHERE [' + @CCOLUMN_NAME
              + N']' + @CWHERECHAR;
        exec sp_executesql @CSQL, N'@ICOUNT INT OUT', @ICOUNT out;
        if @ICOUNT >= 1
        begin
            print 'SELECT ''' + @CTABLE_SCHEMA + '.' + @CTABLE_NAME + ''' AS TABLE_NAME ,' --在生成的查询脚本中加上表架构和表名
                  + '* FROM [' + @CTABLE_SCHEMA + '].[' + @CTABLE_NAME + '] WHERE [' + @CCOLUMN_NAME + ']'
                  + @CWHERECHAR;
        fetch next from CUR_SEARCH
        into @CTABLE_SCHEMA
           , @CTABLE_NAME