数据库中搜索字符串_使用游标.sql

USE db

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(600),@TableRowCount int,@ColumnKey nvarchar(128),@DbName nvarchar(50);
SET @TableName = ''

WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = '';
        set @ColumnKey = '';
        SELECT @TableName = MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), @DbName =min(TABLE_CATALOG)
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE = 'BASE TABLE'
          AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
          AND OBJECTPROPERTY(
                      OBJECT_ID(
                              QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                          ), 'IsMSShipped'
                  ) = 0

        if (@TableName IS NOT NULL and PARSENAME(@TableName, 1) != 'ScanResult')
            BEGIN
                SELECT @TableRowCount = SUM(st.row_count)
                FROM sys.dm_db_partition_stats st
                WHERE object_name(object_id) = PARSENAME(@TableName, 1)
                  AND (index_id < 2)

                select @ColumnKey = column_name
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
                  AND TABLE_NAME = PARSENAME(@TableName, 1)
                  AND DATA_TYPE IN ('int')
                  and ORDINAL_POSITION = 1

                print 'key:'+@ColumnKey

                if @TableRowCount < 20000 and @ColumnKey != ''
                    begin
                        SET @ColumnName =
                                (
                                    SELECT COLUMN_NAME + ','
                                    FROM INFORMATION_SCHEMA.COLUMNS
                                    WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
                                      AND TABLE_NAME = PARSENAME(@TableName, 1)
                                      AND DATA_TYPE IN ('varchar', 'nvarchar')
                                    for xml path('')
                                )

                        print 'cols: '+@ColumnName

                        declare @CursorSql nvarchar(500);
                        declare @Json nvarchar(4000);
                        declare @TableKey int;
                        set @CursorSql = 'declare cur CURSOR for select ' + @ColumnKey + ', (select ' + @ColumnName +
                                         @ColumnKey +
                                         ' for json path,WITHOUT_ARRAY_WRAPPER) from ' + @TableName + ' with (nolock) ';
                        print @CursorSql
                        exec sp_executesql @CursorSql
                        open cur
                        fetch next from cur into @tablekey,@Json;

                        while @@fetch_status = 0 begin
                            if exists(select * from ScanKey where @json like '%' + KeyStr + '%') and not exists (select * from ScanResult
                                where TableKey=@TableKey and TableName=quotename(@DbName)+'.'+ @TableName)
                                begin
                                    insert into ScanResult (tablekey, jsonresult, tablename, createtime)
                                    values (@TableKey, @Json, quotename(@DbName)+'.'+ @TableName, getdate())

                                end

                            fetch next from cur into @TableKey,@Json
                        end
                        close cur;
                        deallocate cur;

                    end
            END
    END


select *
from ScanResult