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

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

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

在运行任何脚本之前，您都应该根据要搜索的数据类型自定义脚本。如果你知道你正在搜索datetime列，那么就没有必要通过nvarchar列进行搜索。这将加快上面所有查询的速度。

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

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

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

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