sqlserver存储过程_sql server中全库搜索存储过程

--全库搜索字符
ALTER PROC [dbo].[P_FIND_CHAR1]
    (
      @CFINDSTR NVARCHAR(MAX) ,
      @I BIT = 1--@I=1精确查找,@I=0 like 查找，默认精确查找
    )
AS
    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 = 'SELECT @ICOUNT=COUNT(*) FROM [' + @CTABLE_SCHEMA
                    + '].[' + @CTABLE_NAME + '] WHERE [' + @CCOLUMN_NAME + ']'
                    + @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 PROC [dbo].[P_FIND_NUM2] ( @NFIND_NUM MONEY )--查找数值
AS
    BEGIN
        DECLARE @CSQL NVARCHAR(MAX);
        DECLARE @CWHERECHAR NVARCHAR(MAX);
        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) IN ('BIT','TINYINT','SMALLINT','INT','DECIMAL','NUMERIC', 'FLOAT','SMALLMONEY','MONEY','FLOAT','REAL' )--限定数据类型
                    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 = ' =' + CAST(@NFIND_NUM AS NVARCHAR(255));--在这里有强制类型转换


                SET @CSQL = 'SELECT @ICOUNT=COUNT(*) FROM [' + @CTABLE_SCHEMA
                    + '].[' + @CTABLE_NAME + '] WHERE [' + @CCOLUMN_NAME + ']'
                    + @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;