This Procedure/Query is used to search list of strings available in Oracle/SQL server database. The strings to be searched should be passed as a parameter to the Procedure/Query. The results will be entered in a temporary table with the Table name and field name where the strings are found in the database.
CREATE PROCEDURE strCheck (@strck varchar(100)) AS create table tbl3( num int) create table TEMP_rec(tblName varchar(50),colName varchar(50)) Declare @tblName as varchar(25) Declare @colName as varchar(25) declare @colType as varchar(25) declare @sql as varchar(200) declare @num as int Declare tblcur cursor for select table_name as Name from INFORMATION_SCHEMA.Tables open tblcur fetch next from tblcur into @tblName while @@fetch_status=0 begin Declare colcur cursor for SELECT column_Name,Data_Type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tblName open colcur fetch next from colcur into @colName,@coltype while @@fetch_status=0 begin if @coltype='char' or @coltype='varchar' begin set @sql='' set @sql='select 1 from ' select @sql = @sql + @tblName + ' where ' + @colname + ' like ' + '''%' + @strck + '%''' insert into tbl3 exec (@sql) if exists (select * from tbl3) begin delete from tbl3 insert into TEMP_rec select @tblName,@colName end end fetch next from colcur into @colName,@coltype end close colcur deallocate colcur fetch next from tblcur into @tblName end close tblcur deallocate tblcur select * from TEMP_rec drop table tbl3 drop table TEMP_rec GO
Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this."Oracle Training in Bangalore"