How search all columns of all tables for a keyword?

[former_SQLA_member1694941]

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...

[asa25]

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,

[Breck_Carter]

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 🙂

[former_SQLA_member1694885]

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

[Baron]

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;

[former_SQLA_member1694941]

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.

[former_SQLA_member1694885]

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