SQL Server Stored Procedure

[pankajit09]

The below is an Oracle Stored procedure -->

create or replace function search_in_table(
 
            owner varchar2,
 
            table_name varchar2,
 
            search_string varchar2)
 
            return varchar2 is
 
            stmt varchar2(32700);
 
            col varchar2(32700);
 
begin
 
            stmt := 'select ';
 
            for c in (
 
                        select column_name
 
                        from all_tab_columns
 
                        where all_tab_columns.table_name=search_in_table.table_name
 
                                    and all_tab_columns.owner=search_in_table.owner
 
                                    and data_type like '%CHAR%' )
 
            loop
 
                        stmt := stmt || 'max(case when REGEXP_LIKE("' || c.column_name ||  '", ''' || search_string || ''') then ''' || c.column_name || ' ''' || ' else null end) || ';
 
            end loop;
 
            stmt:= stmt||'nvl2(count(*),null,null) from "'||owner||'"."'||table_name||'"';
 
            execute immediate stmt into col;
 
            return col;
 
end;

The below Query uses the above function -->

select table_name,cols from (
 
            select rownum,
 
            table_name,
 
            search_in_table('SYSMAN',table_name,'[[:digit:]]') cols
 
            from all_tables where owner='SYSMAN' and tablespace_name='SYSTEM')
 
            where cols is not null;

Please convert the function and the Query to SQL Server Stored Procedure and Query respectively.

[debasisdas]

Please find related discussion here .