在数据库中的任意位置查找值

This might help you . - 来自Narayana Vyas。它搜索给定数据库中所有表的所有列。我以前用过,而且很有效。

这是上面链接中的存储过程-我所做的唯一更改是用temp表替换表变量,这样您就不必每次都记得删除它。

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'

如果您只需要运行一次这样的搜索,那么您可能可以使用其他答案中已经显示的任何脚本。但除此之外,我建议使用 ApexSQL Search 为了这个。这是一个免费的SSMS插件,它真的为我节省了很多时间。

根据bnkdev的回答,我修改了 Narayana's Code 搜索所有列,甚至是数字列。

它会运行得更慢,但这个版本实际上会找到所有匹配项,而不仅仅是在文本列中找到的匹配项。

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


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 QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(CONVERT(varchar(max), ' + @ColumnName + '), 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE CONVERT(varchar(max), ' + @ColumnName + ') LIKE ' + @SearchStr2
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM #Results
END

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

可以使用伪代码描述 select * from * where any like '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

我优化了艾伦·拉隆德的回答( https://stackoverflow.com/a/436676/412368 ). 仍然支持数值。应该大约快4-5倍(1:03比4:30),在带有7GB数据库的桌面上测试。 http://developer.azurewebsites.net/2015/01/mssql-searchalltables/

IF OBJECT_ID ('dbo.SearchAllTables', 'P') IS NOT NULL 
    DROP PROCEDURE dbo.SearchAllTables;
GO

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
-- Customized and modified: 2014-01-21
-- Tested on: SQL Server 2008 R2

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256)
DECLARE @ColumnName nvarchar(128)
DECLARE @DataType nvarchar(128)

DECLARE @SearchStr2 nvarchar(110)
DECLARE @SearchDecimal decimal(38,19)
DECLARE @Query nvarchar(4000)
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')
SET @SearchDecimal = CASE WHEN ISNUMERIC(@SearchStr) = 1 THEN CONVERT(decimal(38,19), @SearchStr) ELSE NULL END
PRINT '@SearchStr2: ' + @SearchStr2
PRINT '@SearchDecimal: ' + CAST(@SearchDecimal AS nvarchar)

SET @TableName = ''
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))
                    DATA_TYPE
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar',
                                  'int', 'bigint', 'tinyint', 'numeric', 'decimal')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )
        SET @DataType =
        (
            SELECT DATA_TYPE
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND QUOTENAME(COLUMN_NAME) = @ColumnName
        )
        PRINT @TableName + '.' + @ColumnName + ' (' + @DataType + ')'

        IF @ColumnName IS NOT NULL
        BEGIN
            IF @DataType IN ('int', 'bigint', 'tinyint', 'numeric', 'decimal')
            BEGIN
                IF @SearchDecimal IS NOT NULL
                BEGIN
                    SET @Query = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(CAST(' + @ColumnName + ' AS nvarchar(110)), 3630) ' +
                                 'FROM ' + @TableName + ' (NOLOCK) ' +
                                 ' WHERE ' + @ColumnName + ' = ' + CAST(@SearchDecimal AS nvarchar)
                    PRINT '    ' + @Query
                    INSERT INTO @Results
                    EXEC (@Query)
                END
            END
            ELSE
            BEGIN
                SET @Query = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) ' +
                             'FROM ' + @TableName + ' (NOLOCK) ' +
                             ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                PRINT '    ' + @Query
                INSERT INTO @Results
                EXEC (@Query)
            END
        END
    END 
END

SELECT ColumnName, ColumnValue FROM @Results
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

我以前有一个解决方案,我一直在改进。如果要求在XML列中进行搜索,也可以在XML列中进行搜索;如果提供仅限整数的字符串,则搜索整数值。

/* Reto Egeter, fullparam.wordpress.com */

DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
SET @SearchStrColumnValue = '%searchthis%' /* use LIKE syntax */
SET @FullRowResult = 1
SET @FullRowResultRows = 3
SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
SET @SearchStrInXML = 0 /* Searching XML data may be slow */

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))

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 TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)
IF @TableName IS NOT NULL
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2)
AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1)
AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END + ',COLUMN_NAME)'
INSERT INTO @ColumnNameTable
EXEC (@sql)
WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
BEGIN
PRINT @ColumnName
SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),'''
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + '''
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
INSERT INTO #Results
EXEC(@sql)
IF @@ROWCOUNT > 0 IF @FullRowResult = 1
BEGIN
SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +
' FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
EXEC(@sql)
END
DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
END 
END
END
SET NOCOUNT OFF

SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
GROUP BY TableName, ColumnName, ColumnValue, ColumnType

资料来源: http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/

谢谢你提供了非常有用的脚本。

如果表中有不可转换的字段,则可能需要对代码添加以下修改:

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 NOT IN ('text', 'image', 'ntext')                 
            AND QUOTENAME(COLUMN_NAME) > @ColumnName
    )

克里斯

我曾经为自己写过一个工具来做这件事:

a7 SqlTools

它是免费的、开源的:

github link

使用连接和光标的另一种方法:

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

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

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';

如果你有 数据库管理 搜索

选择您的数据库。

确保您选择了数据库,而不是表,否则您将得到一个完全不同的搜索对话框。

1. 点击 搜索 标签
2. 列表项选择所需的搜索词
3. 选择要搜索的表

您可能需要构建一个 inverted index 为您的数据库。这肯定是相当快的。

-- 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

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

假设您想要获取所有包含列名的表 登录时间 在数据库中 我的数据库

    use MyDatabase

    SELECT t.name AS table_name,
    SCHEMA_NAME(schema_id) AS schema_name,
    c.name AS column_name
    FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
    WHERE c.name LIKE '%logintime%'
    ORDER BY schema_name, table_name;

数据库客户端工具(如 DBeaver phpMyAdmin )通常支持通过整个数据库进行全文搜索。

我在寻找一个仅仅是一个数值=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