-- exec pSearchAllTables 'M54*'

ALTER PROC pSearchAllTables (@SearchStr NVARCHAR(100))
AS
BEGIN
    -- A procedure to search all tables in a database for a value
    -- Note: Use * or % for wildcard

    DECLARE 
        @Results TABLE([Schema.Table.ColumnName] NVARCHAR(370), ColumnValue NVARCHAR(3630))

    SET NOCOUNT ON

    DECLARE 
        @TableName NVARCHAR(256) = ''
        , @ColumnName NVARCHAR(128)     
        , @SearchStr2 NVARCHAR(110) = QUOTENAME(REPLACE(@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 
        [Schema.Table.ColumnName]
        , ColumnValue 
    FROM @Results
    GROUP BY 
        [Schema.Table.ColumnName]
        , ColumnValue 

END

这是我解决这个问题的方法。在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

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

这是上面链接中的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'

这是我对这个问题的独立看法，我用在我自己的工作中。它可以在SQL2000及更高版本中工作，允许通配符、列过滤，并搜索大多数常规数据类型。

伪代码描述可以选择* from *，其中任何类似'foo'的地方

--------------------------------------------------------------------------------
-- Search all columns in all tables in a database for a string.
-- Does not search: image, sql_variant or user-defined types.
-- Exact search always for money and smallmoney; no wildcards for matching these.
--------------------------------------------------------------------------------
declare @SearchTerm nvarchar(4000) -- Can be max for SQL2005+
declare @ColumnName sysname

--------------------------------------------------------------------------------
-- SET THESE!
--------------------------------------------------------------------------------
set @SearchTerm = N'foo' -- Term to be searched for, wildcards okay
set @ColumnName = N'' -- Use to restrict the search to certain columns, wildcards okay, null or empty string for all cols
--------------------------------------------------------------------------------
-- END SET
--------------------------------------------------------------------------------

set nocount on

declare @TabCols table (
      id int not null primary key identity
    , table_schema sysname not null
    , table_name sysname not null
    , column_name sysname not null
    , data_type sysname not null
)
insert into @TabCols (table_schema, table_name, column_name, data_type)
    select t.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE
    from INFORMATION_SCHEMA.TABLES t
        join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_SCHEMA = c.TABLE_SCHEMA
            and t.TABLE_NAME = c.TABLE_NAME
    where 1 = 1
        and t.TABLE_TYPE = 'base table'
        and c.DATA_TYPE not in ('image', 'sql_variant')
        and c.COLUMN_NAME like case when len(@ColumnName) > 0 then @ColumnName else '%' end
    order by c.TABLE_NAME, c.ORDINAL_POSITION

declare
      @table_schema sysname
    , @table_name sysname
    , @column_name sysname
    , @data_type sysname
    , @exists nvarchar(4000) -- Can be max for SQL2005+
    , @sql nvarchar(4000) -- Can be max for SQL2005+
    , @where nvarchar(4000) -- Can be max for SQL2005+
    , @run nvarchar(4000) -- Can be max for SQL2005+

while exists (select null from @TabCols) begin

    select top 1
          @table_schema = table_schema
        , @table_name = table_name
        , @exists = 'select null from [' + table_schema + '].[' + table_name + '] where 1 = 0'
        , @sql = 'select ''' + '[' + table_schema + '].[' + table_name + ']' + ''' as TABLE_NAME, * from [' + table_schema + '].[' + table_name + '] where 1 = 0'
        , @where = ''
    from @TabCols
    order by id

    while exists (select null from @TabCols where table_schema = @table_schema and table_name = @table_name) begin

        select top 1
              @column_name = column_name
            , @data_type = data_type
        from @TabCols
        where table_schema = @table_schema
            and table_name = @table_name
        order by id

        -- Special case for money
        if @data_type in ('money', 'smallmoney') begin
            if isnumeric(@SearchTerm) = 1 begin
                set @where = @where + ' or [' + @column_name + '] = cast(''' + @SearchTerm + ''' as ' + @data_type + ')' -- could also cast the column as varchar for wildcards
            end
        end
        -- Special case for xml
        else if @data_type = 'xml' begin
            set @where = @where + ' or cast([' + @column_name + '] as nvarchar(max)) like ''' + @SearchTerm + ''''
        end
        -- Special case for date
        else if @data_type in ('date', 'datetime', 'datetime2', 'datetimeoffset', 'smalldatetime', 'time') begin
            set @where = @where + ' or convert(nvarchar(50), [' + @column_name + '], 121) like ''' + @SearchTerm + ''''
        end
        -- Search all other types
        else begin
            set @where = @where + ' or [' + @column_name + '] like ''' + @SearchTerm + ''''
        end

        delete from @TabCols where table_schema = @table_schema and table_name = @table_name and column_name = @column_name

    end

    set @run = 'if exists(' + @exists + @where + ') begin ' + @sql + @where + ' print ''' + @table_name + ''' end'
    print @run
    exec sp_executesql @run

end

set nocount off

我没有把它放在proc形式中，因为我不想在数百个db中维护它，而且它实际上是用于临时工作的。请随意评论错误修复。

您可能需要为数据库构建一个倒立索引。它肯定是相当快的。

在这里，非常甜蜜和小的解决方案:

1) create a store procedure:

create procedure get_table
@find_str varchar(50)
as 
begin
  declare @col_name varchar(500), @tab_name varchar(500);
  declare @find_tab TABLE(table_name varchar(100), column_name varchar(100));

  DECLARE tab_col cursor for 
  select C.name as 'col_name', T.name as tab_name
  from sys.tables as T
  left outer join sys.columns as C on  C.object_id=T.object_id
  left outer join sys.types as TP on  C.system_type_id=TP.system_type_id
  where type='U' 
  and TP.name in('text','ntext','varchar','char','nvarchar','nchar');

  open tab_col
  fetch next from tab_col into @col_name, @tab_name

  while @@FETCH_STATUS = 0
  begin        
    insert into @find_tab 
    exec('select ''' +  @tab_name + ''',''' + @col_name + ''' from ' + @tab_name + 
    ' where ' + @col_name + '=''' + @find_str + ''' group by ' + 
    @col_name + ' having count(*)>0');

    fetch next from tab_col into @col_name, @tab_name;
  end
  CLOSE tab_col;  
  DEALLOCATE tab_col; 
  select table_name, column_name from @find_tab;

end

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

2) call procedure by calling store procedure:
exec get_table 'serach_string';