April 13, 2012 at 3:44 am
Dear all,
i am relatively new to SQL and Sequel Server 2008, but am getting the hang of things. I am setting up a query for our system admin which profiles users.
One of the sets of data that needs to be returned is for unvalidated forms. The design of the application we use is such that there are around 100 forms, each of which may have a number of subforms. All of the tables for the forms and subforms start with "userassess".
Where a form has been configured for user validation, the tables will have a column called "system_ValidationData".
My initial thought was to create an array variable, use a select query to populate the array with the table names, and then use the SQL equivalent of a For Next loop, with the array variable as the table. Then I discovered that SQL doesn't support arrays.
My next thought was that it might be possible to do something with cursors and iterating through all tables which start with "userassess".
My final thought is that I write out 300 or so select queries and insert the results into a temporary table then query this at the end. This would work but would be very labour intensive and would need to be revised every time the software is updated.
Has anyone got any ideas how I could do this dynamically and relatively efficiently?
Iain
April 13, 2012 at 4:35 am
Look up Common Table Expressions and Google spforeachTable (First link is here: http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm). See if that helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply