sql - Looping through tables and querying each one which has TRelationcode in it -

i'm having trouble code loops through tables contain trelationcode. when finds 1 has relationcode , convert new relationcode , update new one.

to create new relationcode i've made function called makerelationcode(oldrelation). have code loop through tables:

dim query string = "use fmsstage; select * information_schema.columns column_name = 'trelationcode'" dim mycmd sqldataadapter = new sqldataadapter(query, con) dim mydata new dataset() mycmd.fill(mydata)  each table datatable in mydata.tables     each row datarow in table.rows         each col datacolumn in table.columns          next     next next 

but need update old codes new ones.

i prefer simple sql commands , little vb logic skipped sqldataadapter part. cost performance , necessary if display in grid , want two-way-binding.

the following code untested , typed blind please check typos etc. put in 1 method.

dim tablenames new list(of string) 'key: old code, value: new code' dim trelationcodes new dictionary(of string, string)  using conn new sqlclient.sqlconnection("yourconnectionstring") 'change connection string needs'     dim qtablenames = "select table_name information_schema.columns column_name = 'trelationcode'"     conn.open()      'get table names trelationcode column'     using commtablenames new sqlclient.sqlcommand(qtablenames, conn)         dim datareader = commtablenames.executereader()         while datareader.read()             tablenames.add(datareader.getstring(0))         end while     end using      'select distinct old trelationcode updated'     dim qtrelationcodesold = "select distinct trelationcode {0}"     each tablename in tablenames         'get old trelationcodes table found previuosly'         using commtrelationcodesold new sqlclient.sqlcommand()             commtrelationcodesold.connection = conn             commtrelationcodesold.commandtext = string.format(qtrelationcodesold, tablename)             dim datareader = commtrelationcodesold.executereader()             while datareader.read()                 dim code = datareader.getstring(0)                 if not trelationcodes.containskey(code)                     trelationcodes.add(code, "") 'value set later'                 end if             end while         end using          'get new trelationcodes'         each trelcodeold in trelationcodes.keys             trelationcodes(trelcodeold) = makerelationcode(trelcodeold)         next          'set new trelationcodes'         dim utrelationcode = "update {0} set trelationcode = @newcode trelationcode = @oldcode"         each trelcodes in trelationcodes             using commtrelationcodesnew new sqlclient.sqlcommand()                 commtrelationcodesnew.connection = conn                 commtrelationcodesnew.commandtext = string.format(utrelationcode, tablename)                 commtrelationcodesnew.parameters.add("@oldcode", sqldbtype.varchar).value = trelcodes.key 'varchar correct?'                 commtrelationcodesnew.parameters.add("@newcode", sqldbtype.varchar).value = trelcodes.value 'varchar correct?'                 commtrelationcodesnew.executenonquery()             end using         next     next end using 

the code far away being optimal, e.g. skipped exception handling.
concerning part makerelationcode function. if logic inside written in t-sql in stored procedure, overall coding simplified.