SQL Server Performance Forum – Threads Archive

find and replace across db

Is it posible to search across all tables in a db for a certain column containing data that I would like to replace. I know that i could look at each table seperately and do this, but i have a bunch of tables that contain the column in question. I guess an example may help clear it up some. I have a table "address" that has a bunch of records. The primary key is AddressID and the two of the records have addressID’s of "0032" and "0053" respectively. The second one was entered by mistake and is a duplicate of the first. AddressID is a foreign key in several other tables, and "0053" is already attached to a couple. I want to delete "0053", but before i do, i have to replace all the references with "0032" so that i don’t get any foreign key violations. Is it possible to do this in one step? Search across the entire db, find all tables with a column "addressID" containing "0053" and replace it with "0032"? Thanks in advance, Ben

I don’t think you can do this in one step. But can be done thru stored procedure. 1) First identify all the tables that references AddressID field. System table sysreferences should help you in this. You can also use INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS for this.
2) In those child tables , check for existence and then update with correct value.
3) Now you should be able to drop the rows.

Refer this also
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm Madhivanan Failing to plan is Planning to fail

there is a tool called SQL digger (it is a free tool) where you can search all across the databases. But I haven’t came across a which does the replacing.
You can find details for the tool at the third party tools section —————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka

Vyas’s script is higly helpful in this case, it helped me earlier. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thank you everyone. Vyas’s script did help quite a bit. It didn’t solve it in one step, but takes a lot of the searching out of the picture, saving me a lot of time. Thanks again, Ben

Ben, You could split it into two stages and search information_schema views for all columns called Address_ID which are a foreign key. And then update those columns where they have the value you don’t like. I haven’t used information_schema for this purpose but something like select ccu.table_name, ccu.column_name
from information_schema.constraint_column_usage ccu
inner join information_schema.table_constraints tc
on ccu.constraint_name = tc.constraint_name
where tc.constraint_type = ‘Foreign Key’
and ccu.column_name = ‘Address_id’ May get you the list of tables and columns. You could then change the select statement to generate your SQL strings to do the update. such as the following:
select ‘UPDATE ‘ + ccu.table_name + ‘ SET ‘ + ccu.column_name + ‘ = ‘ + ”” + ‘0032’ + ”” + ‘ WHERE ‘ + ccu.column_name + ‘ = ‘ + ”” + ‘0053’ + ”” from information_schema.constraint_column_usage ccu
inner join information_schema.table_constraints tc
on ccu.constraint_name = tc.constraint_name
where tc.constraint_type = ‘Foreign Key’
and ccu.column_name = ‘Address_id’ I can’t vouch for using the right tables for this query, but I am sure someone else in the forum will chip in. I wouldn’t however search the entire database for the string ‘0053’ as this could take a while. Regards, Robert.

That works very well. There is so much about SQL that I am clueless about. I keep asking to be able to take a class on it, but my manager here at work is a firm believer in learning as you go. Kind of annoying. Anyway, thank you very much for your help. The output goes to the screen, and then i copy and paste it into a new window to run it. Is there a better way to do this? Thanks again, Ben

Hey Ben,
I have found that i learnt HEAPS more from asking questions and reading on this forum and learning as i go than any SQL or SQL Server courses i have attended! ‘I reject your reality and substitute my own’ – Adam Savage

]]>