建议用 ------------------------第三种方法--------------------- declare @name nvarchar(100) declare cur cursor for select name from sysobjects where type = 'U' open cur fetch next from cur into @name WHILE @@FETCH_STATUS = 0 begin declare @sql nvarchar(500),@s varchar(500) set @s ='' set @sql='select @s=isnull(@s+''+'','''')+'''''',''''''+''+cast(''+name+'' as varchar)'' from syscolumns where id=object_id('''+@name+''') and xtype in(175,239,99,231,35,167) ' exec sp_executesql @sql,N'@s varchar(500) out',@s out if len(@s) > 0 exec ('if exists(select 1 from (select '+ @s+' as col from ['+@name+']) b where charindex(''aa'',col)>0) print '''+@name+'''') fetch next from cur into @name end close cur DEALLOCATE cur------第4种方法---------(来自小梁)CREATE TABLE tb(id int,col varchar(20)) CREATE TABLE tb2(id int,data varchar(20))INSERT tb VALUES(1,'中国'); INSERT tb VALUES(2,'liangck')INSERT tb2 VALUES(1,'China') GOCREATE PROCEDURE dbo.FindString @string NVARCHAR(100) AS DECLARE @SQL NVARCHAR(4000); SET @SQL = N' DECLARE @str NVARCHAR(4000); SELECT @str = ISNULL(@str + N'' OR '' + c.name + N'' LIKE N''''%' + @string + ' %'''''', c.name + N'' LIKE N''''%' + @string +'%'''''') FROM syscolumns AS c JOIN systypes AS t ON c.id=OBJECT_ID(''?'') AND c.xtype=t.xtype AND t.name IN(''varchar'',''char'',''nvarchar'',''nchar''); SET @str = ''SELECT TOP 1 1 FROM ? WHERE ''+@str; CREATE TABLE #tb(a int); INSERT #tb(a) EXEC(@str); IF EXISTS(SELECT * FROM #tb) PRINT ''?'' '; EXEC sp_MsforeachTable @SQL; GOEXEC dbo.FindString N'中国'GO DROP PROCEDURE dbo.FindString DROP TABLE tb,tb2 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fredrickhu/archive/2009/09/20/4574197.aspx
------------------------第三种方法---------------------
declare @name nvarchar(100)
declare cur cursor for select name from sysobjects where type = 'U'
open cur
fetch next from cur into @name
WHILE @@FETCH_STATUS = 0
begin declare @sql nvarchar(500),@s varchar(500)
set @s =''
set @sql='select @s=isnull(@s+''+'','''')+'''''',''''''+''+cast(''+name+'' as varchar)'' from syscolumns where id=object_id('''+@name+''') and xtype in(175,239,99,231,35,167) '
exec sp_executesql @sql,N'@s varchar(500) out',@s out
if len(@s) > 0
exec ('if exists(select 1 from (select '+ @s+' as col from ['+@name+']) b where charindex(''aa'',col)>0) print '''+@name+'''')
fetch next from cur into @name
end
close cur
DEALLOCATE cur------第4种方法---------(来自小梁)CREATE TABLE tb(id int,col varchar(20))
CREATE TABLE tb2(id int,data varchar(20))INSERT tb VALUES(1,'中国');
INSERT tb VALUES(2,'liangck')INSERT tb2 VALUES(1,'China')
GOCREATE PROCEDURE dbo.FindString
@string NVARCHAR(100)
AS DECLARE @SQL NVARCHAR(4000);
SET @SQL = N'
DECLARE @str NVARCHAR(4000);
SELECT
@str = ISNULL(@str + N'' OR '' + c.name + N'' LIKE N''''%'
+ @string + ' %'''''',
c.name + N'' LIKE N''''%' + @string +'%'''''') FROM syscolumns AS c JOIN systypes AS t ON c.id=OBJECT_ID(''?'')
AND c.xtype=t.xtype
AND t.name IN(''varchar'',''char'',''nvarchar'',''nchar''); SET @str = ''SELECT TOP 1 1 FROM ? WHERE ''+@str;
CREATE TABLE #tb(a int);
INSERT #tb(a) EXEC(@str);
IF EXISTS(SELECT * FROM #tb)
PRINT ''?''
';
EXEC sp_MsforeachTable @SQL;
GOEXEC dbo.FindString N'中国'GO
DROP PROCEDURE dbo.FindString
DROP TABLE tb,tb2
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fredrickhu/archive/2009/09/20/4574197.aspx