日期:2014-05-17 浏览次数:20872 次
declare @str varchar(100) set @str='a' --要搜索的字符串 declare @s varchar(8000) declare tb cursor local for select 'if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'') print '' ['+b.name+'].['+a.name+']''' from syscolumns a join sysobjects b on a.id=b.id where b.xtype='U' and a.status>=0 and a.xusertype in(175,239,231,167) open tb fetch next from tb into @s while @@fetch_status=0 begin exec(@s) fetch next from tb into @s end close tb deallocate tb
------解决方案--------------------
搜索指定数据在那个对象中存在.txt CREATE PROC sp_ValueSearch @value sql_variant, --要搜索的数据 @precision bit=1 --1=仅根据sql_variant中的数据类型查找对应类型的数据列.<>1,查询兼容的所有列,字符数据使用like匹配 AS SET NOCOUNT ON IF @value IS NULL RETURN --数据类型处理 SELECT xtype INTO #t FROM systypes WHERE name=SQL_VARIANT_PROPERTY(@value,N'BaseType') --扩展数据类型及查询处理语句 DECLARE @sql nvarchar(4000),@sql1 nvarchar(4000) IF @precision=1 SET @sql=CASE SQL_VARIANT_PROPERTY(@value,N'BaseType') WHEN N'text' THEN N' LIKE N''%''+CAST(@value as varchar(8000))+''%''' WHEN N'ntext' THEN N' LIKE ''%''+CAST(@value as nvarchar(4000))+''%''' ELSE N'=@value' END ELSE BEGIN SET @sql=CAST(SQL_VARIANT_PROPERTY(@value,N'BaseType') as sysname) IF @sql LIKE N'%char' or @sql LIKE N'%text' BEGIN INSERT #t SELECT xtype FROM systypes WHERE name LIKE N'%char' or name LIKE N'%text' SELECT @sql=N' LIKE N''%''+CAST(@value as ' +CASE WHEN LEFT(@sql,1)=N'n' THEN ' nvarchar(4000)' ELSE 'varchar(8000)' END +N')+N''%''' END ELSE IF @sql LIKE N'%datetime' BEGIN INSERT #t SELECT xtype FROM systypes WHERE name LIKE N'%datetime' SET @sql=N'=@value' END ELSE IF @sql LIKE N'%int' OR @sql LIKE N'%money' OR @sql IN(N'real',N'float',N'decimal',N'numeric') BEGIN INSERT #t SELECT xtype FROM systypes WHERE name LIKE N'%int' OR name LIKE N'%money' OR name IN(N'real',N'float',N'decimal') SET @sql=N'=@value' END ELSE SET @sql=N'=@value' END --保存结果的临时表 CREATE TABLE #(TableName sysname,FieldName sysname,Type sysname,SQL nvarchar(4000)) DECLARE tb CURSOR LOCAL FOR SELECT N'SELECT * FROM ' +QUOTENAME(USER_NAME(o.uid)) +N'.'+QUOTENAME(o.name) +N' WHERE '+QUOTENAME(c.name) +@sql, N'INSERT # VALUES(N'+QUOTENAME(o.name,N'''') +N',N'+QUOTENAME(c.name,N'''') +N',N'+QUOTENAME(QUOTENAME(t.name)+CASE WHEN t.name IN (N'decimal',N'numeric') THEN N'('+CAST(c.prec as varchar)+N','+CAST(c.scale as varchar)+N')' WHEN t.name=N'float' OR t.name like N'%char' OR t.name like N'%binary' THEN N'('+CAST(c.prec as varchar)+N')' ELSE N'' END,N'''') +N',@sql)' FROM sysobjects o,syscolumns c,systypes t,#t tt WHERE o.id=c.id AND c.xusertype=t.xusertype AND t.xtype=tt.xtype AND OBJECTPROPERTY(o.id,N'IsUserTable')=1 OPEN tb FETCH tb INTO @sql,@sql1 WHILE @@FETCH_STATUS=0 BEGIN SET @sql1=N'IF EXISTS('+@sql+N') '+@sql1 EXEC sp_executesql @sql1,N'@value sql_variant,@sql nvarchar(4000)',@value,@sql FETCH tb INTO @sql,@sql1 END CLOSE tb DEALLOCATE tb SELECT * FROM # GO
------解决方案--------------------
法二:只包含char,nchar,varchar,nvarchar --------------------------- declare @str varchar(100) set @str='a' --要搜索的字符串 declare @s varchar(8000) declare tb cursor local for select 'if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'') print '' ['+b.name+'].['+a.name+']''' from syscolum