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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy