question

siera_gld avatar image
siera_gld 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 ;
sqlviewinformation_schemadynamic creation
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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.
0 Likes 0 ·
They are all the same, I am also creating tables with a script so there is little chance of failure.
0 Likes 0 ·

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
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
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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?
0 Likes 0 ·
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
0 Likes 0 ·
@Kev Riley ♦♦ I am not sure. I know that it still looks strange because if you have a look at the home page, it will say "0 Votes 0 Answers" for this question. Because of it, I am not even sure that the answer is visible to other users and to OP or not.
0 Likes 0 ·
@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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.