在数据库中查找任何值

Question

给定一个数字，我该如何发现它在哪张表和哪一列中？

速度不是问题，只要能用就行。

- Allain Lalonde

---

你想要在所有列/行中搜索特定的数字吗？你能将其限制为数字列吗？整数列？标识列？ - Michael Haren

所有的列最好都翻译，但数字列也可以。标识列太具体了。 - Allain Lalonde

你可能需要编写一个简短的脚本来查询数据库中的元数据（在这种情况下是表/列列表），并发出一系列选择语句以查找值。 - Draemon

为什么不直接倒出数据并在倒出的数据中进行搜索？这虽然是古老的方法，但很实用。 - MrSynAckSter

phpMyAdmin可以非常简单地允许这一点。 - Matoeil

Answer 1

这可能对你有所帮助。 - 来自Narayana Vyas。它可以搜索给定数据库中所有表的所有列。我以前用过它，它很有效。

这是来自上述链接的存储过程 - 我做出的唯一更改是将临时表替换为表变量，这样您就不必记住每次删除它。

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'

- bnkdev

---

FYI，此脚本仅搜索文本字段，而不是数字字段。在我的情况下，它可以工作，因为开发人员将数字存储在varchar中，但通常查找数字是行不通的。 - Allain Lalonde

能否使用单个查询来完成这个任务，而不是使用存储过程？ - Freakyuser

这里唯一的缺点是，如果每个表的所有列一次性检查（以避免在相同数据页上进行多次读取），那么效率会更高。再次强调，我认为在上述脚本中进行此优化很容易（我之前已经做过类似的事情）。 - Gerardo Lima

如果我们想让该列包含两个搜索字符串怎么办？我的意思是所有包含不同行中的两个字符串的列。 - Pvria Ansari

有没有人知道如何将它迁移到使用 JavaScript 语言的 Snowflake 上？那将是超级棒的。 - data_runner

Answer 2

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

在运行任何脚本之前，您应该根据要搜索的数据类型进行自定义。如果您知道要搜索的是日期时间列，则无需搜索nvarchar列。这将加快上面所有查询的速度。

- David Smithers

---

我花了一些时间才弄清楚在哪里进行此操作，任何阅读此评论的人都可以通过更改来自已接受答案的这个部分来在查询中完成：AND DATA_TYPE IN ('char'，'varchar'，'nchar'，'nvarchar')。 - Taylor Brown

请注意，虽然ApexSQL要求您为其某些工具付费，但这个工具是免费的。您只需要提供您的电子邮件即可。这是一个非常好的工具，当我挖掘未记录的第三方数据库时，它会帮助我很多 :) - youen

在我看来，这应该是最佳答案。过滤不同数据类型的能力非常棒。我唯一对它有意见的是，“选择/取消选择要搜索的表”GUI 是一个带有没有全选/取消全选选项或多选和切换功能的复选框列表。因此，如果您想取消所有系统对象（默认情况下为已选中，这很愚蠢），您必须进行一系列繁琐的操作。但值得庆幸的是，它足够高效，您​不必​这样做。只需让它搜索并完成其工作即可！ - NateJ

ApexSQL Search绝对是最好的解决方案。我刚试着使用脚本在数据库中查找电子邮件地址，但8:30分钟后，我放弃了。安装了ApexSQL Search之后，我搜索了完全相同的字符串，它在11个表中找到了31次。我没有准确计时，但用时不到一分钟。 - cyberspy

Answer 3

根据bnkdev的回答，我修改了Narayana的代码，以便搜索所有列，包括数值列。

虽然速度会慢一些，但这个版本实际上可以找到所有匹配项，而不仅仅是在文本列中找到的。

我对这个人感激不尽，他帮我省去了手动搜索数天的时间！

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

- Allain Lalonde

---

针对“Insufficient result space to convert uniqueidentifier value to char”错误进行了编辑以解决问题。现在，这也适用于XML列。 - Chris

这是一个新问题：不允许从数据类型图像显式转换为varchar(max)。我将尝试自己解决，但如果有人先于我解决了，请告诉我，谢谢！ - Taylor Brown

好的，我刚刚从kd7的代码中添加了一段内容，只搜索我需要的数据类型，这将使那些图像列不在我的搜索范围内，从而导致错误消失...并且数据类型不包括（'image'）。 - Taylor Brown

在SQL Server 2014中出现“将一个或多个字符从XML转换为目标排序规则不可能”的错误。 - Chetan Mehra

这太棒了。 - Ashandra Singh

Answer 4

这是我对这个问题的独立看法，适用于我的工作。它适用于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

我不将它放在proc表单中，因为我不想在数百个数据库中维护它，并且这仅仅是为了临时工作。请随意评论错误修复。

- Tim Lehner

---

谢谢，但我在phpMyAdmin中遇到了语法错误。自从这篇文章写出来以后，SQL有什么变化吗？ - NoBugs

这段话是指这份代码是使用 T-SQL 语言编写的，适用于 SQL Server 数据库。 - Tim Lehner

@Fernando68，你把这个做成存储过程了吗？我回答中的临时批处理没有参数。 - Tim Lehner

只是为了澄清输出，如果从Microsoft SQL Server Management Studio运行此程序，则仅当找到搜索项时，结果选项卡才会弹出。如果未找到搜索项，则仅打开执行的搜索语句的消息选项卡。消息选项卡不包含任何结果，但在找到搜索项时也会与结果选项卡一起打开。 - gakera

非常直接的方法，非常感谢你。 - Andrew

Answer 5

我优化了Allain Lalonde的答案 (https://dev59.com/RXRC5IYBdhLWcg3wAcbU#436676)。仍支持数字值。在桌面上测试了一个7GB数据库，速度应该大约快4-5倍 (1:03 vs 4:30)。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

- Lauri Lubi

Answer 6

我有一个解决方案，之前一直在不断改进。如果被告知这样做，它还可以在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

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

- regeter

---

这是唯一一个在我的权限范围内有效的答案，它不仅可以搜索字符串，而且不会破坏我的表格。 - Pedro Lacerda

这是唯一一个对我有效的。其他的都给了我一个算术溢出错误，将nvarchar转换为数值数据类型。我怀疑是从Oracle带入SQL Server的大型id号导致了这个结果。没有尝试诊断。 - Pearl

对于权限有限的人来说，这是一个绝佳的解决方案！但要注意，这可能需要很长时间（在大型复杂数据库中可能需要数小时）。因此，在它运行时可以做其他事情。在某些情况下，添加USE语句以限制搜索上下文可能会有所帮助。 - pstraton

在我尝试过的所有选项中，这个在速度和准确性方面都是最好的。 - Enzo

Answer 7

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

- Aleksandr Fedorenko

Answer 8

感谢这个非常有用的脚本。

如果您的表格具有不可转换的字段，您可能需要向代码添加以下修改：

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
    )

克里斯

- Rab_

Answer 9

如果您安装了phpMyAdmin，请使用其 搜索 功能。

选择您的数据库。

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

1. 点击 搜索 标签
2. 选择您想要搜索的术语
3. 选择要搜索的表格

- Anil Gupta

Answer 10

这里有一个非常简单易懂的解决方案:

非常甜蜜又小巧:

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

- Vikram Jain