>Hi all, sorry for the vague title, I need to remove *some* records from all tables that have a column called e.g "MyColumn" , my criteria is - if the tables column has a value that matches one in a lookup table then I want to remove them. So I need to get a list of all the tables that have this *column* - then remove records that match my lookup table. I found this code which lists all the tables but I don't know how to proceed from here :-(
>
>
>SELECT COLUMN_NAME, TABLE_NAME
>FROM INFORMATION_SCHEMA.COLUMNS
>WHERE COLUMN_NAME LIKE '%MyName%'
>
>
>any ideas ?
The simplest way will be to select the above into a temp table and loop through it using a cursor. Hopefully you don't have many tables and for few rows cursor approach is Ok.
See this blog post as how to code cursor logic
The Truth about Cursors - Part 1Alternative approach, assuming that code is very similar for all tables, will be to generate that statement dynamically based on your initial query. I've done it a lot, but you should have a good idea of the final command you need to arrive to.
If it's not broken, fix it until it is.
My Blog