搜索数据库中所有表中的所有列以查找特定值

如何搜索数据库中每个表中的所有列以查找特定值？ 这个问题一直不断出现。以下是这个问题的变体，讨论了MSDN论坛T-SQL。不幸的是，没有简单的方法来做到这一点，你必须循环在数据库中的所有表，并搜索值。 可以想象，如果你有一个庞大的数据库，有很多表，这可能需要很长的时间。 此 proc 中正在进行一些检查，如果数据不是数字，则跳过所有数字列，不搜索这些列。此代码由我们自己的 gmmastros 创建，他所做的是创建 3 个不同的存储过程，一个用于日期、数字和字符串。然后，他创建了名为 FindMyData 的第 4 个存储的 proc，此过程将仅在数据类型正确时调用相应的子 procs。 我们只列出存储值的表名和列名称，我们不会返回该数据！ 首先创建以下 3 个存储过程 只是日期搜索 proc

CREATE PROCEDURE FindMyData_Date
    @DataToFind DATETIME
AS
SET NOCOUNT ON
 
DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)

DECLARE @ISDATE BIT
 

 
    INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
    SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
    FROM    Information_Schema.Columns AS C
            INNER Join Information_Schema.Tables AS T
                ON C.Table_Name = T.Table_Name
		AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
    WHERE   Table_Type = 'Base Table'
            And (Data_Type = 'DateTime'
            Or (Data_Type = 'SmallDateTime' And @DataToFind >= '19000101' And @DataToFind < '20790607'))
 
DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)
 
SELECT  @SQLTemplate = 'If Exists(Select *
                                 From   ReplaceTableName
                                 Where  [ReplaceColumnName]
                                              = ''' + CONVERT(VARCHAR(30), @DataToFind, 126) + '''
                                 )
                           Set @DataExists = 1
                       Else
                           Set @DataExists = 0',
        @PARAMETERS = '@DataExists Bit OUTPUT',
        @i = 1
 
SELECT @i = 1, @MAX = MAX(RowId)
FROM   @Temp
 
WHILE @i <= @MAX
    BEGIN
        SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
        FROM    @Temp
        WHERE   RowId = @i
 
 
        PRINT @SQL
        EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT
 
        IF @DataExists =1
            UPDATE @Temp SET DataFound = 1 WHERE RowId = @i
 
        SET @i = @i + 1
    END
 
SELECT  SchemaName,TableName, ColumnName
FROM    @Temp
WHERE   DataFound = 1

go

如果要仅测试此 proc，请尝试

exec FindMyData_Date '20070615'

这是字符串 proc

CREATE PROCEDURE FindMyData_String
    @DataToFind NVARCHAR(4000),
    @ExactMatch BIT = 0
AS
SET NOCOUNT ON
 
DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)
 
    INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
    SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
    FROM    Information_Schema.Columns AS C
            INNER Join Information_Schema.Tables AS T
                ON C.Table_Name = T.Table_Name
		AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
    WHERE   Table_Type = 'Base Table'
            And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')
 
 
DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)
 
SELECT  @SQLTemplate = CASE WHEN @ExactMatch = 1
                            THEN 'If Exists(Select *
                                           From   ReplaceTableName
                                           Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                        = ''' + @DataToFind + '''
                                           )
                                      Set @DataExists = 1
                                  Else
                                      Set @DataExists = 0'
                            ELSE 'If Exists(Select *
                                           From   ReplaceTableName
                                           Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                        Like ''%' + @DataToFind + '%''
                                           )
                                      Set @DataExists = 1
                                  Else
                                      Set @DataExists = 0'
                            END,
        @PARAMETERS = '@DataExists Bit OUTPUT',
        @i = 1
 
SELECT @i = 1, @MAX = MAX(RowId)
FROM   @Temp
 
WHILE @i <= @MAX
    BEGIN
        SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
        FROM    @Temp
        WHERE   RowId = @i
 
 
        PRINT @SQL
        EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT
 
        IF @DataExists =1
            UPDATE @Temp SET DataFound = 1 WHERE RowId = @i
 
        SET @i = @i + 1
    END
 
SELECT  SchemaName,TableName, ColumnName
FROM    @Temp
WHERE   DataFound = 1
GO

如果要仅测试此 proc，请尝试

exec FindMyData_string 'google', 0

只是数字 proc

CREATE PROCEDURE FindMyData_Number
    @DataToFind NVARCHAR(4000),
    @ExactMatch BIT = 0
AS
SET NOCOUNT ON
DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)

DECLARE @IsNumber BIT
DECLARE @ISDATE BIT
 
IF ISNUMERIC(CONVERT(VARCHAR(20), @DataToFind)) = 1
    SET @IsNumber = 1
ELSE
    SET @IsNumber = 0
 
    INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
    SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
    FROM    Information_Schema.Columns AS C
            INNER Join Information_Schema.Tables AS T
                ON C.Table_Name = T.Table_Name
        AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
    WHERE   Table_Type = 'Base Table'
            And Data_Type In ('float','real','decimal','money','smallmoney','bigint','int','smallint','tinyint','bit')
 
 
DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)
 
SELECT  @SQLTemplate = CASE WHEN @ExactMatch = 1
                            THEN 'If Exists(Select *
                                           From   ReplaceTableName
                                           Where  Convert(VarChar(40), [ReplaceColumnName])
                                                        = ''' + @DataToFind + '''
                                           )
                                      Set @DataExists = 1
                                  Else
                                      Set @DataExists = 0'
                            ELSE 'If Exists(Select *
                                           From   ReplaceTableName
                                           Where  Convert(VarChar(40), [ReplaceColumnName])
                                                        Like ''%' + @DataToFind + '%''
                                           )
                                      Set @DataExists = 1
                                  Else
                                      Set @DataExists = 0'
                            END,
        @PARAMETERS = '@DataExists Bit OUTPUT',
        @i = 1
 
SELECT @i = 1, @MAX = MAX(RowId)
FROM   @Temp
 
WHILE @i <= @MAX
    BEGIN
        SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
	FROM    @Temp
        WHERE   RowId = @i
 
 
        PRINT @SQL
        EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT
 
        IF @DataExists =1
            UPDATE @Temp SET DataFound = 1 WHERE RowId = @i
 
        SET @i = @i + 1
    END
 
SELECT  SchemaName,TableName, ColumnName
FROM    @Temp
WHERE   DataFound = 1
 
go

如果要仅测试此 proc，请尝试

exec FindMyData_Number '562', 1

CREATE PROCEDURE FindMyData
    @DataToFind NVARCHAR(4000),
    @ExactMatch BIT = 0
AS
SET NOCOUNT ON
 
CREATE TABLE #Output(SchemaName sysname, TableName sysname, ColumnName sysname)
 
IF ISDATE(@DataToFind) = 1
    INSERT INTO #Output EXEC FindMyData_Date @DataToFind
 
IF ISNUMERIC(@DataToFind) = 1
    INSERT INTO #Output EXEC FindMyData_Number @DataToFind, @Exactmatch
 
INSERT INTO #Output EXEC FindMyData_String @DataToFind, @ExactMatch
 
SELECT SchemaName,TableName, ColumnName
FROM   #Output
ORDER BY SchemaName,TableName, ColumnName
go

下面是一些测试它 proc 调用

exec FindMyData 'google', 0
exec FindMyData 1, 0
exec FindMyData '20081201', 0
exec FindMyData 'sysobjects', 0