November 15, 2013 at 1:03 am
Comments posted to this topic are about the item Search Table And Field In All Database And Return Field Value
November 20, 2013 at 12:13 pm
I get the below when I run it, I am a newbie so might be me. Using 2008 SQL. No Results.
(2 row(s) affected)
(7 row(s) affected)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
(0 row(s) affected)
(0 row(s) affected)
November 24, 2013 at 11:22 pm
Yes ,I'm Sorry ,Forget Collate
Declare @TableNameParameter Nvarchar(200)='UserInfo'
Declare @FieldNameParameterTable Table
(
FieldsName Nvarchar(200)
)
Insert into @FieldNameParameterTable(FieldsName) values ('UserName'),('Password')
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(200)
)
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 top(1) value from @TempValue)
where RowId=@Iterator
Set @Iterator=@Iterator+1
End
Select * from @TempTable
November 25, 2013 at 12:08 am
Declare @TableNameParameter Nvarchar(200)='DimGeography'
Declare @FieldNameParameterTable Table
(
FieldsName Nvarchar(200)
)
Insert into @FieldNameParameterTable(FieldsName) values ('City'),('StateProvinceCode')
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 + ',' + value
FROM @TempValue
FOR
XML PATH('')
), 1, 1, '') AS XYList)
where RowId=@Iterator
Set @Iterator=@Iterator+1
End
Select * from @TempTable
November 25, 2013 at 12:33 am
Declare @TableNameParameter Nvarchar(200)='Address'
Declare @FieldNameParameterTable Table
(
FieldsName Nvarchar(200)
)
Insert into @FieldNameParameterTable(FieldsName) values ('AddressLine1'),('AddressLine2')
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply