Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58
Declare @keytosearch varchar(max), @Database_Selected varchar(50) set @keytosearch ='%london%' set @Database_Selected= 'Northwind' Declare @Table varchar(100), @Table_Name Cursor, @Count_Column int, @Result nvarchar(4000),@ID int,@ID_inserted int,@Count_Table int, @data_type varchar(10) set @ID_inserted=0 set @Count_Table=0 DECLARE @column varchar(max), @Column_Name CURSOR --Variable Delaration end --Second Cursor start declare @informationName varchar(50), @SysName varchar(50), @Var varchar(5) set @informationName=@Database_Selected+'.'+'information_schema.COLUMNS' Set @SysName=@Database_Selected+'.'+'sys.objects' Set @Var='u' --Database Selected start Create Table #SearchResult(ID int identity(1,1) not null, TableName varchar(50), ColumnName varchar(50)) Create Table #SearchTestResult(ID int identity(1,1) not null, ColumnName varchar(max)) Create Table #Table_Name(ID int identity(1,1) not null, table_name varchar(50)) IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Column_Nam') drop table Column_Nam insert into #Table_Name exec ('Select name from '+@SysName +' where type= '''+@Var+'''') --First Cursor open SET @Table_Name = CURSOR FOR Select table_name from #Table_Name open @Table_Name Fetch Next from @Table_Name into @Table WHILE @@FETCH_STATUS = 0 BEGIN set @Count_Table =@Count_Table+1 --Second cursor opened --print 'Select column_name from '+@informationName +' where table_name= '''+@Table+''''s Exec('SELECT column_name,data_type INTO Column_Nam FROM '+ @informationName +' where table_name = '''+@Table+'''') SET @Column_Name = CURSOR FOR (select column_name from Column_Nam ) OPEN @Column_Name FETCH NEXT FROM @Column_Name INTO @column set @Table=@Database_Selected+'.[dbo].['+@Table+']' WHILE @@FETCH_STATUS = 0 BEGIN set @data_type= (SELECT top 1 data_type FROM Column_Nam Where column_name= @column) if @data_type is not null and (@data_type='varchar' or @data_type='nvarchar' or @data_type='Text') begin set @Result=null if @column like '%-%' begin set @Result =('SELECT ''' + @column +''' FROM ' + @Table +' Where ''' + @column + ''' Like '''+@keytosearch+'''') end else set @Result =('SELECT ' + @column +' FROM ' + @Table +' Where ' + @column + ' Like '''+@keytosearch+'''') insert into #SearchTestResult exec(@Result) set @ID=0 set @ID=(Select ID from #SearchTestResult where ID=Scope_identity()) if @ID is not null begin set @ID_inserted=(select top 1 ID from #SearchResult order by ID desc) if @ID_inserted = @ID begin print '' end else insert into #SearchResult values (@Table,@column) end end FETCH NEXT FROM @Column_Name INTO @column END CLOSE @Column_Name DEALLOCATE @Column_Name --Second cursor closed drop table Column_Nam Fetch Next from @Table_Name into @Table End close @Table_Name Deallocate @Table_Name --First Cursor Closed Select * from #SearchResult