asked
Dynamic Maintenance of a view
I would like to create a routine that maintains a view which us a union of the same columns in tables. Since I am allowing the end user to create tables based on a workflow, some tables have not been created yet. in my workflow, I've identified the naming convention of the table structure, noted below. does anyone here have experience with this, can you lead me in the right direction of creating a script dynamically that basically does Select Col1 , Col2, Col3 from TableA UNION Select Col1 , Col2, Col3 from TableB --If table B does not exist, it wont be in the schema_information.Tables heres what I have so far, compliments of google :) IF OBJECT_ID('tempdb..#VT_TEMP') IS NOT NULL DROP TABLE #VT_TEMP SELECT t.TABLE_NAME AS TableName , max(ccount.ORDINAL_POSITION) ROW_COUNT , ccount.ORDINAL_POSITION AS ROW_RANK , ccount.COLUMN_NAME as COL INTO #VT_TEMP FROM information_schema.tables t INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME INNER JOIN ( SELECT TABLE_NAME ,COLUMN_NAME , ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS ) ccount ON t.TABLE_NAME = ccount.TABLE_NAME INNER JOIN [db_name].[dbo].[MSA_DATA_SUBMIT_SCHED] sch -- USER DRIVEN WORKFLOW HERE on 'MSA_'+sch.[PROCESS_MNENONIC] = t.TABLE_NAME where t.TABLE_NAME like 'PROC%' group by t.TABLE_NAME , ccount.ORDINAL_POSITION , ccount.COLUMN_NAME ORDER BY t.TABLE_NAME, ccount.ORDINAL_POSITION ;
@siera_gld Are the column names the same or different in the tables? Just asking, they don't have to be the same. The number of columns in each table has to be the same though, and their datatypes should match or at least be compatible. Please let me know and I will have the dynamic script tomorrow morning (Central time, USA). It is going to have to be the stored procedure, not the view, but I am sure that this will not be a problem.
Instead of querying the information\_schema views, let me use the sys.tables and sys.columns instead. Assuming that the MSA\_DATA\_SUBMIT\_SCHED has one row per table name, you can join it with sys.tables to "filter out" thouse tables which don't yet exists in the database even though they are already referenced in MSA\_DATA\_SUBMIT\_SCHED table. This join may be then cross applied with sys.columns to get the comma-delimited list of columns. Please note that when the cross apply is used in this manner (to get the comma-delimited list out), you cannot reference the column to be delimited by name because if you do then the xml nodes named like the alias will be included in the delimited list and you don't want that. On the other hand, the "nameless" output of the cross apply must have a name, so the trick is to give the output its name afterwards. This is why you see the **c(List)** which named both the "function" used to cross apply and what it returns. Here is the script which will produce the script you need which you can then execute: declare @sql nvarchar(max) = stuff( ( select 'union all' + char(10) + 'select ' + stuff(c.List, 1, 2, '') + ' from ' + t.name + char(10) from sys.tables t inner join [db_name].[dbo].[MSA_DATA_SUBMIT_SCHED] sch on 'MSA_' + sch.Process_Mnemonic = t.name cross apply ( select ', ' + name from sys.columns where [object_id] = t.[object_id] order by column_id for xml path('') ) c(List) for xml path('') ), 1, 9, ''); -- select @sql; /* uncomment to sneak peak how the generated query looks like */ -- execute the generated script exec(@sql); Hope this helps, Oleg
Can someone please shed some light on this mystery with answers, I have not seen this before. It looks like whenever I add an answer, it is not available for some time. For example, I just typed in the answer but the home page still lists "0 answers" for this question. Is it because I used to be a part of moderators group in the past and then was bumped out because I was out of the site for so long? Or is it a new rule which we did not have before?
did it go into moderation? We have a lot more going in there now as the rules were tightened in an attempt to reduce the spam
@Kev Riley ♦♦ Well, it looks like the answer has no votes, bit is now marked as accepted. This means that it is visible, at least to moderators, OP and me (as the one who answered). It is just a little strange to see how it looks on the home page with text reading "**0 answers**" yet with the background colour identifying that there is an accepted answer.
19 People are following this question.
