有什么方法可以在Microsoft SQL Server的每个表的每个字段中找到特定值？

有什么方法可以在Microsoft SQL Server的每个表的每个字段中找到特定值？

您可以尝试以下存储过程来在每个表的每个字段中搜索特定值：

CREATE PROC sp_SearchAllTables(    @SearchStr nvarchar(100))ASBEGINCREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))SET NOCOUNT onDECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)SET  @TableName = ''SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')WHILE @TableName IS NOT NULLBEGIN    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', 'int', 'decimal')     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    ENDSELECt ColumnName, ColumnValue FROM #ResultsEND

然后像这样使用它：

exec sp_SearchAllTables 'your search text here';