在所有数据库中搜索价值

我已经按照以下更新了您的逻辑，并且工作正常，请看一下：

DECLARE @SearchStr VARCHAr(50)='Surat'DECLARE @Results TABLE(DatabaseName NVARCHAr(500),  TableName nvarchar(370),ColumnName nvarchar(370), ColumnValue nvarchar(3630),PrimaryKey nvarchar(200), PrimaryKeyValue nvarchar(4000))SET NOCOUNT onDECLARE @TableList AS Table(    TableName VARCHAr(500),    RowNo INT)DECLARE @ColumnList AS Table(    ColumnName VARCHAr(500),    RowNo INT)DECLARE @PrimaryKeyList AS Table(    PrimaryKeyName VARCHAr(500))DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @PrimaryKey nvarchar(200), @CurrentTableName nvarchar(256)SET @TableName = ''SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')DECLARE @DatabaseCount INT=0, @Index INT=0, @DatabaseName NVARCHAr(500), @TotalColumnCount INT, @ColumnIndex INT=0, @TotalTableCount INT, @TableIndex INT=0SELECt     *,    ROW_NUMBER() OVER (ORDER BY name) AS RowNo INTO #tblDatabases FROM Sys.DatabasesWHERe name NOT IN ('master','model','msdb','tempdb')SELECt @DatabaseCount=COUNT (*) FROM #tblDatabasesWHILE @Index<@DatabaseCountBEGIN    SET @Index=@Index+1    SELECt @DatabaseName='',@TableIndex=0,@ColumnIndex=0,@TableName='',@ColumnName=''    SELECT @DatabaseName=name FROM #tblDatabases WHERe RowNo=@Index    DELETe FROM @TableList    INSERT INTO @TableList    EXEC(' SELECt QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME),     ROW_NUMBER() OVER (ORDER BY TABLE_NAME) FROM ['+@DatabaseName+'].INFORMATION_SCHEMA.TABLES WHERe TABLE_TYPE = ''base TABLE''        ')    SELECt @TotalTableCount=COUNT(*) FROM @TableList    WHILE @TableIndex<@TotalTableCount     BEGIN        SET @TableIndex=@TableIndex+1        SELECt @ColumnName = '',@ColumnIndex=0        SELECT @TableName=TableName FROM @TableList  WHERe RowNo=@TableIndex         SET @CurrentTableName=REPLACe(REPLACE(REPLACE(@TableName,'[dbo].',''),'[',''),']','')        DELETE FROM @ColumnList        INSERT INTO @ColumnList        EXEC('SELECt     COLUMN_NAME,ROW_NUMBER() OVER (ORDER BY COLUMN_NAME)         FROM ['+@DatabaseName+'].INFORMATION_SCHEMA.COLUMNS     WHERe TABLE_SCHEMA = PARSENAME('''+@TableName+''', 2)         AND TABLE_NAME = PARSENAME('''+@TableName+''', 1)         AND DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'')        ')        SELECt @TotalColumnCount=COUNT(*) FROM @ColumnList        WHILE @ColumnIndex<@TotalColumnCount        BEGIN SET @ColumnIndex=@ColumnIndex+1 SET @ColumnName='' SELECt @ColumnName=ColumnName FROM @ColumnList WHERe RowNo=@ColumnIndex DELETe FROM @PrimaryKeyList INSERT INTO @PrimaryKeyList EXEC('         SELECt Col.Column_Name from   ['+@DatabaseName+'].INFORMATION_SCHEMA.TABLE_ConSTRAINTS Tab,   ['+@DatabaseName+'].INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col          WHERe   Col.Constraint_Name = Tab.Constraint_Name  AND Col.Table_Name = Tab.Table_Name  AND Constraint_Type = ''PRIMARY KEY''  AND Col.Table_Name= '''+@CurrentTableName+'''     ') SELECt @PrimaryKey='' SELECT @PrimaryKey=PrimaryKeyName FROM @PrimaryKeyList SET @PrimaryKey=ISNULL(@PrimaryKey,'') IF @ColumnName IS NOT NULL AND @PrimaryKey<>'' BEGIN        INSERT INTO @Results     EXEC     (         'SELECt '''+@DatabaseName+''','''+@CurrentTableName+''',''' + @ColumnName + ''', LEFt(' + @ColumnName + ', 3630) , '''+@PrimaryKey+''', [' + @PrimaryKey + ']          FROM ['+@DatabaseName+'].' + @TableName + ' (NOLOCK) ' +         ' WHERe ' + @ColumnName + ' LIKE ' + @SearchStr2     ) END        END    ENDENDSELECt ROW_NUMBER() OVER (ORDER BY DatabaseName) AS Id,DatabaseName,TableName,ColumnName, ColumnValue AS SearchValue,PrimaryKeyValue AS Pk, PrimaryKey  FROM @ResultsDROP TABLE #tblDatabases