retrieve table and column names from code

Question

I need to retrieve the table names and all of the subsequent columns to be added into the tables to do it manually, given from the code I've got. I can gather all of the table names quite easily (I believe) but I can't see exactly what I'm looking for column wise. I need help knowing the column data and datatype (old mysql database with the tables was lost)

useroptions.vb :http://pastebin.com/84SxTzfz
useroptionscommon.vb :http://pastebin.com/2rxf3e2y
Connections.vb :http://pastebin.com/CpWqt2m8
userpersistence.vb :http://pastebin.com/TaAex24Q
streamprocessor.vb :http://pastebin.com/Tazr1s8p
serverpersistance.vb :http://pastebin.com/qvnCN2PY
frontpagehub.vb :http://pastebin.com/FYyLH4qP

Even if somebody could tell me what to look for to figure out the tables I need to add, columns and datatype of the columns. I would greatly appreciate it. I've spent days now trying to reconstruct the database and find ways to figure out everything I need and I've gotten a few things up and running but it's these in particular I'm having trouble with.

You are working under the premise that the code touches all the database tables and all of the fields in the database, which is probably not the case. In other words, you are going to wind up with holes in the database even if you go line-by-line through the code. — Karl Anderson, Sep 10 '13 at 03:13
how are you going to reconstruct triggers, stored procedures etc? you may have to go through the design document (if any) you have. — bansi, Sep 10 '13 at 03:17
everything is hard coded and all of the tables and entries can be found via error debugging and searching through the code, though I understand in normal cases this is not enough - but in this case it is. But, this has been informative. Thank you both. — user2652705, Sep 10 '13 at 14:45

score 0 · Accepted Answer · answered Sep 10 '13 at 04:39

Search for keywords INSERT, UPDATE, DELETE and SELECT to reach SQL statements in the program files to figure out names of tables and columns of the tables. You can get closest SQL data type information from values supplied to the parameters used in the SQL statements. You may get some idea of what could be the primary keys based on columns in WHERE clause but it requires your judgement and familiarity of intended use of the columns to decide some columns as primary keys as non-primary columns can also be in WHERE clauses.

For example I see the following code in file useroptions.vb:

                Dim Connection As New MySqlConnection(Utils.connectionString) : Connection.Open()
                Dim Command As New MySqlCommand("UPDATE custom_user_data SET `" + name + "`=@value WHERE userID=@userID;", Connection)
                Command.Parameters.AddWithValue("@value", value)
                Command.Parameters.Add("@userID", MySqlDbType.Int32).Value = userID
                Command.ExecuteNonQuery()

Here you find UPDATE statement. The first identifier custom_user_data is the name of a table. Variable name contains one column name you have to figure out. WHERE clause contains column name userID. You can get some SQL data type information from the parameters. Unfortunately I am not a VB programmer and hence I am not a position to give any more info. As somebody pointed out it is almost impossible to get various constraints(primary keys, foreign keys, check constraints), nullability information, triggers, any stored procedures, etc., from the programs. Good luck.

I've managed to get the majority of things working through this and error debugging, it's coming along nicely. I just wish there was a way to collect all data needed. — user2652705, Sep 10 '13 at 14:41

retrieve table and column names from code

1 Answers1