有点晚了，但希望有用。

为什么不尝试一些可以集成到SSMS中的第三方工具呢?

我使用过ApexSQL Search(100%免费)，在模式和数据搜索方面都取得了很好的成功，还有SSMS工具包也有这个功能(SQL 2012不是免费的，但相当实惠)。

上面的存储过程真的很棒;只是在我看来这样更方便。此外，如果你想搜索datetime列或GUID列等，它将需要一些轻微的修改…

有点晚了，但是您可以很容易地用这个查询找到一个字符串

DECLARE
@search_string  VARCHAR(100),
@table_name     SYSNAME,
@table_id       INT,
@column_name    SYSNAME,
@sql_string     VARCHAR(2000)

SET @search_string = 'StringtoSearch'

DECLARE tables_cur CURSOR FOR SELECT ss.name +'.'+ so.name [name], object_id FROM sys.objects so INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id WHERE  type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id 
        AND system_type_id IN (167, 175, 231, 239)

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + '] 
            LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''

            EXECUTE(@sql_string)

        FETCH NEXT FROM columns_cur INTO @column_name
        END

    CLOSE columns_cur

DEALLOCATE columns_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur
DEALLOCATE tables_cur

不要忽略@Brandon的精彩回答，但OP要求“结果应该显示表及其各自的行”。我基于来自@Brandon的原始答案创建了一个记录级解决方案，该解决方案还通过简单地将所有适当的列连接到一个字符串中来避免使用其他人在这里使用的游标。(我感觉这样做会更好，但我真的没有比较过结果-请随意这样做并在这里提供反馈。)

注意，我使用了STRING_AGG函数来连接列。如果你使用的是旧版本的SQL Server，你可能需要使用可以在网上找到的替代方法之一。

CREATE PROC [dbo].[SearchAllTablesByRecord]
(
    @SearchStr NVARCHAR(100)
)
AS
BEGIN            

    SET NOCOUNT ON

    DECLARE @TableName NVARCHAR(256), @SearchStr2 NVARCHAR(110)
    DECLARE @ColumnList NVARCHAR(2048), @ColumnJoin NVARCHAR(2048)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    BEGIN
            SET @TableName = 
            (
                    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                    FROM    INFORMATION_SCHEMA.TABLES
                   WHERE           TABLE_TYPE = 'BASE TABLE'
                            AND     QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                            AND     OBJECTPROPERTY(
                                            OBJECT_ID(
                                                    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                                                     ), 'IsMSShipped'
                                                   ) = 0
            )
            
            PRINT @TableName    -- For progress monitoring
            SET @ColumnList = 
            (
                SELECT STRING_AGG(QUOTENAME(COLUMN_NAME),',') 
                FROM    INFORMATION_SCHEMA.COLUMNS
                WHERE           TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                        AND     TABLE_NAME      = PARSENAME(@TableName, 1)
                        AND     DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
            )
            SET @ColumnJoin = REPLACE(@ColumnList,',','+')
            
            IF @ColumnList <> '' 
                EXEC
                (
                        'SELECT ''' + @TableName + ''' AS [Table],' + @ColumnList + '
                        INTO #Results
                        FROM ' + @TableName + ' (NOLOCK) ' +
                        ' WHERE ' + @ColumnJoin + ' LIKE ' + @SearchStr2 + '
                        
                        IF @@ROWCOUNT > 0 SELECT * FROM #Results'
                )
    END
END

有点晚了，但希望有用。

为什么不尝试一些可以集成到SSMS中的第三方工具呢?

我使用过ApexSQL Search(100%免费)，在模式和数据搜索方面都取得了很好的成功，还有SSMS工具包也有这个功能(SQL 2012不是免费的，但相当实惠)。

上面的存储过程真的很棒;只是在我看来这样更方便。此外，如果你想搜索datetime列或GUID列等，它将需要一些轻微的修改…

不需要嵌套循环(遍历表的外部循环和遍历所有表列的内部循环)。可以从INFORMATION_SCHEMA中检索所有(或任意选择/筛选的)表-列组合。COLUMNS和在一个循环中简单地遍历(搜索)所有它们:

DECLARE @search VARCHAR(100), @table SYSNAME, @column SYSNAME

DECLARE curTabCol CURSOR FOR
    SELECT c.TABLE_SCHEMA + '.' + c.TABLE_NAME, c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS c
    JOIN INFORMATION_SCHEMA.TABLES t 
      ON t.TABLE_NAME=c.TABLE_NAME AND t.TABLE_TYPE='BASE TABLE' -- avoid views
    WHERE c.DATA_TYPE IN ('varchar','nvarchar') -- searching only in these column types
    --AND c.COLUMN_NAME IN ('NAME','DESCRIPTION') -- searching only in these column names

SET @search='john'

OPEN curTabCol
FETCH NEXT FROM curTabCol INTO @table, @column

WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXECUTE('IF EXISTS 
             (SELECT * FROM ' + @table + ' WHERE ' + @column + ' = ''' + @search + ''') 
             PRINT ''' + @table + '.' + @column + '''')
    FETCH NEXT FROM curTabCol INTO @table, @column
END

CLOSE curTabCol
DEALLOCATE curTabCol

[CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS 
        BEGIN 
            
        CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) 
        
        SET NOCOUNT ON 
        
        DECLARE @TableName nvarchar(256), 
                @ColumnName nvarchar(128), 
                @SearchStr2 nvarchar(110) 
                
        SET @TableName = '' 
        SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') 
        
        WHILE @TableName IS NOT NULL 
        BEGIN 
             SET @ColumnName = '' 
             SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) 
                                FROM INFORMATION_SCHEMA.TABLES 
                                WHERE TABLE_TYPE = 'BASE TABLE' 
                                      AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName 
                                      AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 
                              ) 
                                      
         WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) 
         BEGIN 
              SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) 
                                  FROM INFORMATION_SCHEMA.COLUMNS 
                                  WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) 
                                        AND TABLE_NAME = PARSENAME(@TableName, 1) 
                                        AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') 
                                        AND QUOTENAME(COLUMN_NAME) > @ColumnName ) 
               IF @ColumnName IS NOT NULL 
               BEGIN 
                     INSERT INTO #Results 
                     EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) 
               END 
         END 
         END 
         
         SELECT ColumnName, ColumnValue FROM #Results 
         
         END
    
    EXEC SearchAllTables 'Computer'][1]


  [1]: https://koukia.ca/search-for-a-string-in-all-tables-and-all-fields-of-a-database-with-tsql-b6f8a1bfda37