数据库关键字搜索查询html,在所有数据库中搜索关键字

在所有数据库中搜索关键字：

drop table #Retrieve

set nocount on

Declare @UserDB varchar(100)

declare @str varchar(100)

set @str='VGC' --your string to be retrieved

create table #Retrieve(Retrieve_Info varchar(200))

Declare DBName cursor

for

select name from sys.databases where name not in

(N'master', N'model', N'msdb', N'tempdb', N'distribution', N'DWDiagnostics', N'DWConfiguration', N'DWQueue', N'resource',N'ReportServer',N'ReportServerTempDB')

open DBName;

fetch next from DBName into @UserDB;

while @@FETCH_STATUS=0

begin

Declare @column_sql varchar(1000)

set @column_sql=

'DECLARE columnCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +

table_name + '']'' as tableName,''['' + table_catalog + ''].['' + table_schema + ''].['' +

table_name + ''].['' + column_name + '']'' as columnName FROM [' + @UserDB + '].INFORMATION_SCHEMA.columns a

where a.table_NAME in (select name from '+quotename(@UserDB,'[]')+'.sys.tables) and a.table_NAME <>'+'''sysdiagrams'''

--print(@column_sql)

exec(@column_sql)

declare @Column_name varchar(100)

declare @tablename varchar(100)

open columnCursor

fetch next from columnCursor into @tablename,@Column_name;

while @@FETCH_STATUS=0

begin

declare @retrieve varchar(1000)

declare @retrieve2 varchar(1000)

declare @count int

set @retrieve='declare @rowcount int;

select '+@Column_name+' from '+@tablename+' where convert(varchar(200),'+@Column_name+') like '+QUOTENAME('%'+@str+'%','''')

--print (@retrieve)

insert into #Retrieve exec (@retrieve)

select     @count=count(1) from #Retrieve

if @count>0

begin

set @retrieve2='select * from '+@tablename+' where convert(varchar(200),'+@Column_name+') like '+QUOTENAME('%'+@str+'%','''')

--print (@retrieve2)

exec (@retrieve2)

delete from #Retrieve

end

else

continue

fetch next from columnCursor into @tablename,@Column_name;

end

close columnCursor

DEALLOCATE columnCursor

fetch next from DBName into @UserDB;

end

close DBName

DEALLOCATE DBName

set nocount off