这是我解决这个问题的方法。在SQLServer2008R2上测试

CREATE PROC SearchAllTables
@SearchStr nvarchar(100)
AS
BEGIN
DECLARE @dml nvarchar(max) = N''        
IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE dbo.#Results
CREATE TABLE dbo.#Results
 ([tablename] nvarchar(100), 
  [ColumnName] nvarchar(100), 
  [Value] nvarchar(max))  
SELECT @dml += ' SELECT ''' + s.name + '.' + t.name + ''' AS [tablename], ''' + 
                c.name + ''' AS [ColumnName], CAST(' + QUOTENAME(c.name) + 
               ' AS nvarchar(max)) AS [Value] FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) +
               ' (NOLOCK) WHERE CAST(' + QUOTENAME(c.name) + ' AS nvarchar(max)) LIKE ' + '''%' + @SearchStr + '%'''
FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id
                   JOIN sys.columns c ON t.object_id = c.object_id
                   JOIN sys.types ty ON c.system_type_id = ty.system_type_id AND c .user_type_id = ty .user_type_id
WHERE t.is_ms_shipped = 0 AND ty.name NOT IN ('timestamp', 'image', 'sql_variant')

INSERT dbo.#Results
EXEC sp_executesql @dml

SELECT *
FROM dbo.#Results
END

我正在寻找一个数值= 6.84 -使用这里的其他答案，我能够将我的搜索限制在这个范围内

Declare @sourceTable Table(id INT NOT NULL IDENTITY PRIMARY KEY, table_name varchar(1000), column_name varchar(1000))
Declare @resultsTable Table(id INT NOT NULL IDENTITY PRIMARY KEY, table_name varchar(1000))

Insert into @sourceTable(table_name, column_name)
select schema_name(t.schema_id) + '.' + t.name as[table], c.name as column_name
from sys.columns c
join sys.tables t
on t.object_id = c.object_id
where type_name(user_type_id) in ('decimal', 'numeric', 'smallmoney', 'money', 'float', 'real')
order by[table], c.column_id;

DECLARE db_cursor CURSOR FOR
Select table_name, column_name from @sourceTable
DECLARE @mytablename VARCHAR(1000);
DECLARE @mycolumnname VARCHAR(1000);

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @mytablename, @mycolumnname

WHILE @ @FETCH_STATUS = 0
BEGIN
    Insert into @ResultsTable(table_name)
    EXEC('SELECT ''' + @mytablename + '.' + @mycolumnname + '''  FROM ' + @mytablename + ' (NOLOCK) ' +
    ' WHERE ' + @mycolumnname + '=6.84')
    FETCH NEXT FROM db_cursor INTO @mytablename, @mycolumnname  
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
Select Distinct(table_name) from @ResultsTable

使用JOIN和CURSOR的另一种方法:

USE My_Database;

-- Store results in a local temp table so that.  I'm using a
-- local temp table so that I can access it in SP_EXECUTESQL.
create table #tmp (
    tbl nvarchar(max),
    col nvarchar(max),
    val nvarchar(max)   
);

declare @tbl nvarchar(max);
declare @col nvarchar(max);
declare @q nvarchar(max);
declare @search nvarchar(max) = 'my search key';

-- Create a cursor on all columns in the database
declare c cursor for
SELECT tbls.TABLE_NAME, cols.COLUMN_NAME  FROM INFORMATION_SCHEMA.TABLES AS tbls
JOIN INFORMATION_SCHEMA.COLUMNS AS cols
ON tbls.TABLE_NAME = cols.TABLE_NAME

-- For each table and column pair, see if the search value exists.
open c
fetch next from c into @tbl, @col
while @@FETCH_STATUS = 0
begin
    -- Look for the search key in current table column and if found add it to the results.
    SET @q = 'INSERT INTO #tmp SELECT ''' + @tbl + ''', ''' + @col + ''', ' + @col + ' FROM ' + @tbl + ' WHERE ' + @col + ' LIKE ''%' + @search + '%'''
    EXEC SP_EXECUTESQL @q
    fetch next from c into @tbl, @col
end
close c
deallocate c

-- Get results
select * from #tmp

-- Remove local temp table.
drop table #tmp

出于开发目的，您可以将所需的表数据导出到单个HTML中，并对其进行直接搜索。

到目前为止，我发现的最好和最通用的解决方案是通过管道将db的转储传递给您正在搜索的grep。

例如，Mysql:

mysqldump -pPASSWORD database | grep 'search phrase'

或者如果你得到了太多的结果，你可以把它们输出到一个文件:

mysqldump -pPASSWORD database | grep 'search phrase' > results.txt

这可能对你有帮助。——来自纳拉亚纳·维亚斯。它搜索给定数据库中所有表的所有列。我以前用过，效果很好。

这是上面链接中的Stored Proc -我所做的唯一更改是将临时表替换为表变量，这样您就不必每次都记得删除它。

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

DECLARE @Results TABLE(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 + 
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM @Results
END

执行存储过程。

 EXEC SearchAllTables 'YourStringHere'