遍历数据库查找一个关键字解决方法

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 [email protected]+'%'')        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 tbfetch next from tb into @swhile @@fetch_status=0begin    exec(@s)    fetch next from tb into @sendclose tbdeallocate tb
------解决方案--------------------
SQL code
搜索指定数据在那个对象中存在.txtCREATE PROC sp_ValueSearch@value sql_variant,  --要搜索的数据@precision bit=1     --1=仅根据sql_variant中的数据类型查找对应类型的数据列.<>1,查询兼容的所有列,字符数据使用like匹配ASSET NOCOUNT ONIF @value IS NULL RETURN--数据类型处理SELECT xtype INTO #t FROM systypesWHERE 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 [email protected]' ENDELSEBEGIN    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 @[email protected]'        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 @[email protected]'        END    ELSE        SET @[email protected]'END--保存结果的临时表CREATE TABLE #(TableName sysname,FieldName sysname,Type sysname,SQL nvarchar(4000))DECLARE tb CURSOR LOCALFORSELECT N'SELECT * FROM '    +QUOTENAME(USER_NAME(o.uid))    +N'.'+QUOTENAME(o.name)    +N' WHERE '+QUOTENAME(c.name)    [email protected],    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 ttWHERE o.id=c.id    AND c.xusertype=t.xusertype    AND t.xtype=tt.xtype    AND OBJECTPROPERTY(o.id,N'IsUserTable')=1OPEN tbFETCH tb INTO @sql,@sql1WHILE @@FETCH_STATUS=0BEGIN    SET @sql1=N'IF EXISTS([email protected]+N') [email protected]    EXEC sp_executesql @sql1,[email protected] sql_variant,@sql nvarchar(4000)',@value,@sql    FETCH tb INTO @sql,@sql1ENDCLOSE tbDEALLOCATE tbSELECT * FROM #GO
------解决方案--------------------
SQL code
法二：只包含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 [email protected]+'%'')        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 tbfetch next from tb into @swhile @@fetch_status=0begin    exec(@s)    fetch next from tb into @sendclose tbdeallocate tb