I'm dealing with a database structure that can be defined as "illogical". It has about 100 different schema's with all different table structures per schema. Only one common factor is a "Version" table in each schema containing about 4 fields. (Thus, there are about 100 Version tables in the database.) There's also another table (view, actually) containing a list of all the schema's in the database that have a version table.

I need a stored procedure that walks through all the schema's and selects all data from the Version table, adding the schema name as a fifth field to the result. Basically, this stored procedure is to return a list of all version records per schema.

My idea: first walk through the schema list to create one new SQL statements that will JOIN all the schema.version tables into one SQL statement. Then I return the result of that query.

How to do this? Or does anyone have a better suggestion?

(No, redesigning the structure is NOT an option.)