Technical Article

Search Table And Field In All Database And Return Field Value

,

This script will allow you to search table and field in all database and return field value. Below is an example of the what the output will look like.

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

Read 237 times
(14 in last 30 days)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating