Search Table And Field In All Database And Return Field Value

Declare @TableNameParameter Nvarchar(200)='DimScenario'



Declare @FieldNameParameterTable  Table
(
	FieldsName Nvarchar(200)
)
Insert into @FieldNameParameterTable(FieldsName) values ('ScenarioKey'),('ScenarioName')



DECLARE @TempDatabases Table 
(
	id int primary key identity(1,1),
	databaseId int,
	DatabaseName nvarchar(100),
	RowNumber int
)


Insert into @TempDatabases(databaseId,DatabaseName,RowNumber)
Select database_id,Name,ROW_NUMBER()over(order by database_id)
	FROM SYS.databases
	WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb','ReportServer','ReportServerTempDB')
	
	Declare @CountDatabase int
	Select @CountDatabase=COUNT(*) from 	@TempDatabases

	Declare @QueryListTableInDatabase nvarchar(max)
	set @QueryListTableInDatabase=''
	Declare @QueryColumns nvarchar(Max)=''
Declare @iteratorDatabase int=1
	while(@iteratorDatabase<=@CountDatabase)
		begin
			Declare @CurrentDatabaseName nvarchar(200)
				Select @CurrentDatabaseName=DatabaseName
					from @TempDatabases
						where RowNumber=@iteratorDatabase
				
				
				Set @QueryListTableInDatabase=@QueryListTableInDatabase+' Select '''+@CurrentDatabaseName+''' ,Name from '+@CurrentDatabaseName+'.sys.tables  Where Type=''U''  Union All  '
				Set @QueryColumns=@QueryColumns+' SELECT TABLE_CATALOG COLLATE   SQL_Latin1_General_CP1_CI_AS,TABLE_SCHEMA COLLATE   SQL_Latin1_General_CP1_CI_AS,TABLE_NAME COLLATE   SQL_Latin1_General_CP1_CI_AS,COLUMN_NAME COLLATE   SQL_Latin1_General_CP1_CI_AS FROM '+@CurrentDatabaseName+'.INFORMATION_SCHEMA.COLUMNS Where Table_Name='''+@TableNameParameter+'''  Union '
				
				
			Set @iteratorDatabase=@iteratorDatabase+1
		End
	
	Set @QueryColumns=SUBSTRING(@QueryColumns,0,len(@QueryColumns)-5)
	
	
DECLARE @TempTable Table 
(
	RowId int primary key identity(1,1),
	DatabaseName nvarchar(100),
	TABLE_SCHEMA nvarchar(100),
	TABLE_NAME nvarchar(100),
	COLUMN_NAME nvarchar(100),
	Value nvarchar(max)
)
	
	Insert into @TempTable(DatabaseName,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
	exec Sp_Executesql @QueryColumns
	
	Delete from @TempTable
	where COLUMN_NAME Not In (Select * from @FieldNameParameterTable)
	
	
	
	
	Declare @CountRecordTable int
	Declare @Iterator int=1
				
	Select @CountRecordTable=COUNT(*)
		from @TempTable
		
		
		Declare @Query nvarchar(max)=''
		Declare @UpdateQuery nvarchar(max)=''
		while(@Iterator<=@CountRecordTable)
			begin
				
				Declare @CurrentDatabaseName1 nvarchar(100)
				Declare @CurrentTABLE_SCHEMA nvarchar(100)
				Declare @CurrentTABLE_NAME nvarchar(100)
				Declare @CurrentCOLUMN_NAME nvarchar(100)
				Select  @CurrentDatabaseName1=DatabaseName,
						@CurrentTABLE_SCHEMA=TABLE_SCHEMA,
						@CurrentTABLE_NAME=TABLE_NAME,
						@CurrentCOLUMN_NAME=COLUMN_NAME
						From @TempTable
						where RowId=@Iterator
						
					Set @Query='Select '+@CurrentCOLUMN_NAME 
								+' from  '+	@CurrentDatabaseName1+'.'+@CurrentTABLE_SCHEMA+'.'+@CurrentTABLE_NAME 
							
						
						
					
				Declare  @TempValue Table(value nvarchar(200))	
				Delete from @TempValue
				insert into @TempValue(value)
				exec Sp_Executesql @Query
					
																						
					
			
					
					
					Update  @TempTable 
						Set Value=(	SELECT  STUFF(( SELECT ',' + value 
										FROM @TempValue
										FOR
											XML PATH('')
												), 1, 1, '') AS XYList)
						where RowId=@Iterator
					
			Set @Iterator=@Iterator+1
			End
		
		
		Select * from @TempTable