declare @s varchar (1000 )
set @s='подстрока'  

SET NOCOUNT ON  
DECLARE @TableID int, @TableName sysname  
DECLARE @ColumnName sysname, @Type sysname, @IsNullable int  
DECLARE @Columns varchar (8000 ), @Where varchar (8000 )
DECLARE @MarkNum bit  

 -- таблица, содержащая результаты поиска  
 
CREATE TABLE #Result (TableName sysname, String ntext)  

 -- признак того, искать ли среди числовых значений  
 
SET @MarkNum = IsNumeric(@s)

 -- искать подстроку  
 
SET @s='%' + @s + '%'  

 -- курсор по всем пользовательским таблицам базы данных  
 
DECLARE #Table CURSOR LOCAL FAST_FORWARD FOR  

select id, name from sysobjects
where status&gt ;0  and xtype='U'  

OPEN #Table
WHILE @@ERROR =0 

BEGIN  

  FETCH NEXT FROM #Table INTO @TableID, @TableName

  IF @@FETCH_STATUS<&gt ;0  BREAK  

 -- пропустить таблицы, к которым нет доступа  
 

  if IS_SRVROLEMEMBER('dbcreator') =0  AND  

    not EXISTS(select *  

    from INFORMATION_SCHEMA.TABLE_PRIVILEGES a

    join sysusers b on b.name=a.GRANTEE and b.issqlrole =1 

    join sysusers c on c.gid=b.gid and c.issqlrole =0  and c.name=USER_NAME()

    where a.PRIVILEGE_TYPE='SELECT' and a.TABLE_NAME=@TableName

    ) CONTINUE  

  SET @Columns=''  

 -- курсор по всем колонкам текущей таблицы  
 
  DECLARE #Column CURSOR LOCAL FAST_FORWARD FOR  

  select a.name, b.name, a.isnullable from syscolumns a
  join systypes b on b.xtype=a.xtype and b.xusertype&lt ;256 
  where id=@TableID order by colid  

  OPEN #Column
  WHILE @@ERROR =0 

  BEGIN  
    FETCH NEXT FROM #Column INTO @ColumnName, @Type, @IsNullable
    IF @@FETCH_STATUS<&gt ;0  BREAK  

    if (@Type <> 'image' and @Type <> 'bit' and CHARINDEX('text', @Type) =  0 )
      and (@MarkNum =1  or CHARINDEX('char', @Type) >  0  or CHARINDEX('datetime', @Type) >  0 )

    begin  

      if @IsNullable =  1  SET @Columns = @Columns + 'isnull('  
      if (CHARINDEX('char', @Type) >  0 )
        SET @Columns = @Columns + @ColumnName
      else if (CHARINDEX('datetime', @Type) >  0 )  
        SET @Columns = @Columns + 'convert(varchar,' + @ColumnName + ',104)'  
      else  
        SET @Columns = @Columns + 'cast(' + @ColumnName + ' as varchar)'  

      if @IsNullable =  1 
        SET @Columns = @Columns + ','''')+'' | ''+'  
      else  
        SET @Columns = @Columns + '+'' | ''+'  
    end  

  END  

  CLOSE #Column

  DEALLOCATE #Column

  IF @Columns <> ''  
  BEGIN  
    SET @Columns = LEFT(@Columns, LEN(@Columns) -7 )
    SET @Where = @Columns + ' like ''' + @s + ''''  

exec('insert #Result  

select ''' + @TableName + ''',' + @Columns + '
from ' + @TableName + '
where ' + @Where)

  END  
END  

CLOSE #Table
DEALLOCATE #Table
SET NOCOUNT OFF  
SELECT * FROM #Result
DROP TABLE #Result