[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

Stored procedures: how to check all table's columns?

Hi,

i've got an issue - when i import data from oracle, i get a weird symbol '
' char(2) in some cells. i want it to be replaced by _ (underscore). in my opinion, the easyest way to do this is to write a stored procedure which takes a table name as parameter, scans all non-numeric columns for this symbol and replaces it. it should execute sql similar to this:

UPDATE sometable SET column1=REPLACE(column1,char(2),'_'), column4=REPLACE(column4,char(2),'_')

the problem is, each table has different columns, and i can't find a way to get their names and types.

maybe stored procedure is not the best way to solve my problem? any help would be appreciated.
0
tigrine_smiltpele
Asked:
tigrine_smiltpele
  • 8
  • 7
1 Solution
 
Brian CroweCommented:
I think you need to look at how you import the data from Oracle.  It would be much more efficient to intercede at this point to replace problem characters.  How are you importing your data? DTS?
0
 
tigrine_smiltpeleAuthor Commented:
Yes. i use DTS.

more details:
system being imported into: MS SQL server 2000
source system: Oracle 9.2
connection: microsoft odbc driver for oracle
copying of tables is created using 'transform data task'
0
 
LowfatspreadCommented:
you can use the information_schema table and
basically generate the update sql you require...

e.g.


select Case ordinal_position
            when minord
            Then Case T When 1 Then 'UPDATE ['+c.Table_schema+'].['+c.table_name+'] SET ' Else 'Where ' End
            Else Case T When 1 Then ',' else ' OR ' End
            End
      +'['+Column_name+']'
      +Case T When 1 Then '=Replace(['+column_name+'],char(2),''_'')'
              Else ' like ''%''+char(2)+''%'''    
              end
      +Case Ordinal_position
            when Maxord
            Then Case t when 1 then ' From ['+c.Table_schema+'].['+c.table_name+'] '
                        Else ''
                        end
            else ''
            end  

 From information_schema.[columns] as c
 inner join information_schema.[tables] as T
 on c.table_name=t.table_name
 and c.Table_schema=t.Table_schema
 Inner Join (select table_name,Table_schema,min(ordinal_position) as minord
                 ,max(ordinal_position) as maxord
                from information_schema.[columns]
               where data_type like '%char%'
               group by table_name,Table_schema) as M
 on c.table_name=m.table_name
 and c.Table_schema=m.Table_schema
 Cross Join (select 1 as T union select 2) as x
 Where c.Data_type like '%char%'
   and t.table_type='Base Table'
 order by c.Table_schema,c.Table_name,t,ordinal_position



if you need to cater for TextColumns   then you'd need to modify the code to cater form UPDATETEXT statements...

hth  
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
tigrine_smiltpeleAuthor Commented:
Amazing piece of code! i've got only one question - how to execute it? when i put it into a stored procedure and execute in query analyzer, it shows me query in window, but does not execute it. is there a way to execute it?
0
 
LowfatspreadCommented:
i was basically assuming that you would then cut it from the results and
paste it into another query analyser window and execute it from there...

or place the bits you wanted into some other stored procedure....

you could insert the output initially into a temporary table
and then write the data to an 8000 varchar in a loop  
and then execute it if you desired...

but i normally just use these technique to generate static code to then
examine , test and execute...

hth  

 
0
 
tigrine_smiltpeleAuthor Commented:
no no no, i'll put it in DTS package to execute every night after tables are re-imported. is inserting into temporary table the easyest way?
0
 
LowfatspreadCommented:
it depends on how variable the number of tables will be....

if you don't change your table names / modify your table columns then running the code once and pasteing the code into a "static" procedure
would be best...

but if you have a dynamic environment then perhaps doing it this way would be better....

set nocount on

drop table #temp

Declare @r int , @max int
Declare @tbname sysname,@tbowner sysname

select Case ordinal_position
            when minord
            Then Case T When 1 Then 'UPDATE ['+c.Table_schema+'].['+c.table_name+'] SET ' Else 'Where ' End
            Else Case T When 1 Then ',' else ' OR ' End
            End
      +'['+Column_name+']'
      +Case T When 1 Then '=Replace(['+column_name+'],char(2),''_'')'
              Else ' like @LK' --''%''+char(2)+''%'''    
              end
      +Case Ordinal_position
            when Maxord
            Then Case t when 1 then ' From ['+c.Table_schema+'].['+c.table_name+'] '
                        Else ''
                        end
            else ''
            end   as Data
     ,c.Table_schema as Table_owner
     ,C.Table_name
     ,T,Ordinal_position
     ,identity(int,1,1) as R
 Into #Temp
 From information_schema.[columns] as c
 inner join information_schema.[tables] as T
 on c.table_name=t.table_name
 and c.Table_schema=t.Table_schema
 Inner Join (select table_name,Table_schema,min(ordinal_position) as minord
                 ,max(ordinal_position) as maxord
                from information_schema.[columns]
               where data_type like '%char%'
               group by table_name,Table_schema) as M
 on c.table_name=m.table_name
 and c.Table_schema=m.Table_schema
 Cross Join (select 1 as T union select 2) as x
 Where c.Data_type like '%char%'
   and t.table_type='Base Table'
 order by c.Table_schema,c.Table_name,t,ordinal_position

set @max=@@ROWCOUNT

set @r=1

declare @SQL varchar(8000)

While @r<=@max
begin
   Select @tbowner=table_owner,@tbname=table_name
     from #temp
    where R=@r
   select @r=max(r)+1
     from #temp
    Where Table_owner=@tbowner and Table_name = @tbname
      and r>=@r
   Set @SQL='Declare @LK char(7) set @lk=''%''+char(2)+''%'' '
   select @SQL=@SQL+ DATA
     from #temp
    Where table_owner=@tbowner and table_name = @tbname
      and r<@r
    Order by t,ordinal_position
   Print @SQL

   Exec(@SQL)
End  
0
 
tigrine_smiltpeleAuthor Commented:
the tables structures are changing constantly - i'm importing new tables and new columns almost daily, so dynamic execution would be better.

i've added one more line:
if exists (select * from dbo.sysobjects where id = object_id(N'#temp'))
      drop table #temp

when i run the procedure, it displays query which it executes. also, i've removed Where clause completely because it somehow filtered out everything. gonna see how it will impact execution time (the database is quite small - approx 2 mil rows)

the problem is, it only processes first table in database - others remain intact. is there something missing? (a loop, perhaps?)
0
 
LowfatspreadCommented:
sorry i think this is the culprit

Set @SQL='Declare @LK char(7) set @lk=''%''+char(2)+''%'' '

the @lk variable should be 3 characters long not 7
it should be

Set @SQL='Declare @LK char(3) set @lk=''%''+char(2)+''%'' '

and you should be able to leave the where clause in ...

can you post the procedure you're actually running if the above doesn't solve it..
0
 
tigrine_smiltpeleAuthor Commented:
just did the correction - it works fine with WHERE clause now, but still processes only first table.

although SQL in your first post ( Date: 05/02/2006 10:02AM PDT) does display update for ALL tables.
0
 
tigrine_smiltpeleAuthor Commented:
oh, and i'm increasing points since it's difficult question
0
 
LowfatspreadCommented:
it should loop around doing the update against all the tables its identified as having character columns

thats what the

while @r <= @max
begin  
...
end

is doing...

are you saying its actually printing out the sql for each table but not actually making changes in the data?

are you sure that all your tables actually have char(2) characters in them and its not different non display characters in other tables...

e.g. char(20) also displays as a small square box?

can you post the

 while begin ... end

you've got running...

hth
LFS

ps .. I'm in the UK, Experts exchange now displays all post times according to the users local settings so PDT doesn't help me that much

;-)
0
 
tigrine_smiltpeleAuthor Commented:
every table has square as char(2) - i just took a random table and checked.
also, i've exported #temp table into excel and put in rapidshare.de: http://rapidshare.de/files/19624390/oracleproblem.zip.html

when loop is executed first time,
--
  select @r=max(r)+1
     from #temp
    Where Table_owner=@tbowner and Table_name = @tbname
      and r>=@r
--
sets @r to 701 and it naturally stops.


set @max=@@ROWCOUNT

set @r=1

declare @SQL varchar(8000)

While @r<=@max
begin
   Select @tbowner=table_owner,@tbname=table_name
     from #temp
    where R=@r
   select @r=max(r)+1
     from #temp
    Where Table_owner=@tbowner and Table_name = @tbname
      and r>=@r
   Set @SQL='Declare @LK char(3) set @lk=''%''+char(2)+''%'' '
   select @SQL=@SQL+ DATA
     from #temp
    Where table_owner=@tbowner and table_name = @tbname
      and r<@r
    Order by t,ordinal_position
   Print @SQL
   Exec(@SQL)
End
0
 
LowfatspreadCommented:
and i assume the @max is actually 700?

can you run this before the  loop...

select min(r),max(r),table_owner,table_name
 from #temp
group by table_owner,table_name
order by 1,2

also please confirm you have the order by on the initial select

order by c.Table_schema,c.Table_name,t,ordinal_position


its working ok for me...
0
 
tigrine_smiltpeleAuthor Commented:
yes, i had 'order by' clause in initial select.

also, i've run this little query after the initial select:

Select data,table_owner,table_name,T,ordinal_position,identity(int,1,1) as R into #temp2 from #temp order by table_owner,table_name,T,ordinal_position

and changed second loop to operate on #temp2 instead of temp

now everything works fine!
0
 
LowfatspreadCommented:
odd but glad it works for you..
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now