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 .