How search all columns of all tables for a keyword?

Hi,

I Have a large database, with several tables, and obviously, many columns .
Due to an update on my system I need to change all entries where I have  'H:\' to '\server\folder\'.
But I dont know the tables neider columns where this string happen.
There is any way to select all fields in each table where I have this string, this way I can, at least, create some script to update the tables and solve my problem ?
Hope I was clear ??
Thanks a lot.
Paulo Braga
IT Manager not DBA...

asked 23 May '17, 17:19

phbraga
41●7●7●9
accept rate: 0%

flat view

3 Answers:

active answers oldest answers newest answers popular answers

Hi,

You should rebuild the database.

Database rebuilds

http://dcx.sap.com/index.html#sqla170/en/html/8184a0196ce210148e44c2c2c08aa7b0.html

Unload utility (dbunload)

http://dcx.sap.com/index.html#sqla170/en/html/813e16456ce21014b96dc39365129dc6.html

When the database unload, the data are output to a file. You can search & update data with this file. And you reload the data to a new database.

Thanks,

permanent link

answered 24 May '17, 02:28

asa
161●1●2●7
accept rate: 9%

This is an excellent starting point... and it may indeed be a complete solution (brute force is sometimes the best way).

If the unloaded data files are extremely large (e.g., gigabytes) it may be time consuming to edit them to replace a short substring H:\ with a longer one \server\folder... it may be necessary to shop around for a text editor that can handle the task.

However, at the very least you may be able to discover which columns in which tables contain H:\ and use an UPDATE to actually change them.

One alternative may be a FOR SELECT SYSTABCOL loop containing an EXECUTE IMMEDIATE... bonus points for the first such answer that works :)

(24 May '17, 03:10) Breck Carter

Try this:

select string ('select ''', suser_name (t.creator), '.', t.table_name, ''', ''', c.column_name, ''', "', c.column_name,
'" from "', suser_name (t.creator), '"."', t.table_name, '"\n where "', c.column_name, '" like ''%H:\\\\%'';')
from systable t join systabcol c on t.table_id = c.table_id
where t.creator not in (0, 3) -- SYS, dbo
and t.table_type = 'BASE' and remote_location is null
and c.domain_id in (select domain_id from sysdomain where domain_name like '%char%');
output to 'C:\\Temp\\SelPattern.SQL' format text quote '' escapes off hexadecimal asis;
read 'C:\\Temp\\SelPattern.SQL';

You may have to adjust the WHERE clause of the generator query, and of course you don't have to automatically READ the generated script.
If you are satisfied with the result, you may replace the string function call with

string ('update "', suser_name (t.creator), '"."', t.table_name, '" set "', c.column_name,
'" = replace ("', c.column_name, '", ''H:\\'', ''\\\\server\\folder\\'')\n where "', c.column_name, '" like ''%H:\\%'';\ncommit;')

and use a different filename. You may apply a white list of table and column names where at least one match had been found in the first run.

HTH

Volker
DB-TecKnowledgy

(24 May '17, 16:37) Volker DB-TecKy

flat view

Dear Fellows,

Thanks for your time and knowledge, i used some parts of the first idea to solve my issue, I made a script with unload of all tables, creating txt files for each into a folder, then use grep to find the string i needed.

The Volker alternative, unfortunately was not so clear, it created a sql file and then read it but no result was shown..

I thik I solve it, thank you so much.

permanent link

answered 24 May '17, 17:00

phbraga
41●7●7●9
accept rate: 0%

Which dbisql version do you use (17 or older)? If "older", did you check the "show results from all statements" option? Default is "show only result from last statement".

HTH

Volker

(24 May '17, 17:22) Volker DB-TecKy

flat view

I think the following block could help you if you can modify the line 6, so that you can retrict the columnscursor to retrive only the columns whose datatype is (varchar). I personally couldn't find a proper way for restricting the select statement, so that it worked partially but I had error messages whenever it reaches a column with a numeric datatype.

begin

declare currenttablename varchar(128); --maximum length for table names

declare currenttableid int;

declare currentcolumn varchar(128); --maximum length for column names

declare tablescursor dynamic scroll cursor for select table_name, table_id from systable where creator >100;

declare columnscursor dynamic scroll cursor for select column_name from syscolumn where table_id = currenttableid and pkey = 'N';

declare NoDataAvailable exception for sqlstate value '02000';

open tablescursor with hold;

fetch first tablescursor into currenttablename, currenttableid;

tablesloop:

while sqlstate<>NoDataAvailable loop

open columnscursor with hold;

fetch first columnscursor into currentcolumn;

columnsloop:

while sqlstate<>NoDataAvailable loop

message (currenttablename);

message (currenttableid);

execute immediate string('update ' + currenttablename + ' set ' + currentcolumn + '=''new_path'' where ' + currentcolumn + '=''old_path''');

fetch next columnscursor into currentcolumn;

end loop columnsloop;

close columnscursor;

fetch next tablescursor into currenttablename, currenttableid;

end loop tablesloop;

close tablescursor;

end;

permanent link

answered 25 May '17, 06:55

Baron
2.1k●136●149●177
accept rate: 48%

edited 25 May '17, 06:56

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

select ×69
table ×34
string ×22
column ×10

question asked: 23 May '17, 17:19

question was seen: 3,880 times

last updated: 25 May '17, 06:56

SAP SQL Anywhere

How search all columns of all tables for a keyword?

begin

declare currenttablename varchar(128); --maximum length for table names

declare currenttableid int;

declare currentcolumn varchar(128); --maximum length for column names

declare tablescursor dynamic scroll cursor for select table_name, table_id from systable where creator >100;

declare columnscursor dynamic scroll cursor for select column_name from syscolumn where table_id = currenttableid and pkey = 'N';

declare NoDataAvailable exception for sqlstate value '02000';

open tablescursor with hold;

fetch first tablescursor into currenttablename, currenttableid;

tablesloop:

while sqlstate<>NoDataAvailable loop

open columnscursor with hold;

fetch first columnscursor into currentcolumn;

columnsloop:

while sqlstate<>NoDataAvailable loop

message (currenttablename);

message (currenttableid);

execute immediate string('update ' + currenttablename + ' set ' + currentcolumn + '=''new_path'' where ' + currentcolumn + '=''old_path''');

fetch next columnscursor into currentcolumn;

end loop columnsloop;

close columnscursor;

fetch next tablescursor into currenttablename, currenttableid;

end loop tablesloop;

close tablescursor;

end;

Follow this question

Related questions