By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,341 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

SQL Server Stored Procedure

100+
P: 296
The below is an Oracle Stored procedure -->

Expand|Select|Wrap|Line Numbers
  1. create or replace function search_in_table(
  2.  
  3.             owner varchar2,
  4.  
  5.             table_name varchar2,
  6.  
  7.             search_string varchar2)
  8.  
  9.             return varchar2 is
  10.  
  11.             stmt varchar2(32700);
  12.  
  13.             col varchar2(32700);
  14.  
  15. begin
  16.  
  17.             stmt := 'select ';
  18.  
  19.             for c in (
  20.  
  21.                         select column_name
  22.  
  23.                         from all_tab_columns
  24.  
  25.                         where all_tab_columns.table_name=search_in_table.table_name
  26.  
  27.                                     and all_tab_columns.owner=search_in_table.owner
  28.  
  29.                                     and data_type like '%CHAR%' )
  30.  
  31.             loop
  32.  
  33.                         stmt := stmt || 'max(case when REGEXP_LIKE("' || c.column_name ||  '", ''' || search_string || ''') then ''' || c.column_name || ' ''' || ' else null end) || ';
  34.  
  35.             end loop;
  36.  
  37.             stmt:= stmt||'nvl2(count(*),null,null) from "'||owner||'"."'||table_name||'"';
  38.  
  39.             execute immediate stmt into col;
  40.  
  41.             return col;
  42.  
  43. end;

The below Query uses the above function -->

Expand|Select|Wrap|Line Numbers
  1. select table_name,cols from (
  2.  
  3.             select rownum,
  4.  
  5.             table_name,
  6.  
  7.             search_in_table('SYSMAN',table_name,'[[:digit:]]') cols
  8.  
  9.             from all_tables where owner='SYSMAN' and tablespace_name='SYSTEM')
  10.  
  11.             where cols is not null;

Please convert the function and the Query to SQL Server Stored Procedure and Query respectively.
May 2 '08 #1
Share this Question
Share on Google+
1 Reply


debasisdas
Expert 5K+
P: 8,127
Please find related discussion here .
May 5 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.