use pubs 
go
declare @name nvarchar(100)
declare @str nvarchar(100)
set @str = N'Carson' -- 这里输入你的变量值

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(''' + @str + ''',col)>0) print '''+@name+'''')
    fetch next from cur into @name
end
close cur
DEALLOCATE cur

/*
stores
authors

*/

------解决方案--------------------
select name from sysobjects
where id in(select id from syscolumns where name=字段名)