--全库搜索字符
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;