View creation: Using UNION ALL on dynamic schemas

Question

View creation: Using UNION ALL on dynamic schemas

vk1.softcomp

Old Hand

Points: 303
More actions
July 21, 2018 at 2:04 pm

#354929

Hi,
I want to create a VIEW that combines same as well as different tables from different as well as same schemas, something like
CREATE VIEW SCHEMA.VIEWName AS
Select
    someKnowncol1 as Colname1,
    someKnowncol2 as Colname2,
   ...(some more columns..)
from
    UnknownSchemaName1.KnownTableX
UNION ALL
Select
    someKnowncol1 as Colname1,
    someKnowncol2 as Colname2,
   ...(some more columns..)
from
    UnknownSchemaName2.KnownTableX
UNION ALL
--There could be more entries like above ones that could follow..
--If say 5 schemas have this table 'KnownTableX', then 5 SELECT statements and all will be combined using UNION ALL. But its not known how many such schemas will get created and also their name is not known. But whenever it gets created its sure to have this table 'KnownTableX'.
--So, Here number of schemas and name of schema is unknown but the name of the table which they will have is known say 'KnownTableX'
--That is, till here TABLE Names and their structures/column names are all same and identical.

UNION ALL
-- From here onwards the schema name is known and same.... say 'KnownSchemaX'.
-- The Table names and their structure(column names) are known but different.

Select
    someKnowncol3 as Colname1,
    someKnowncol4 as Colname2,
   ...(some more columns..)
from
    KnownSchemaX.KnownTablename3
UNION ALL
Select
    someKnowncol5 as Colname1,
    someKnowncol6 as Colname2,
   ...(some more columns..)
from
    KnownSchemaX.KnownTablenam4
;

Could any expert guide me on how to approach this problem ?
a) When the database and all objects gets created, I could invoke a procedure which will create this View.
b) In future , whenever view is accessed , it should be able to display table data of even the newly added schemas (the unknown ones). and should not give error saying that object doesn't exists.
All schemas(known and unknown) are from the same database.
Thanks
Vijay

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

andycadley SSCertifiable Points: 5296 More actions · Answer 1

It would need to be a stored procedure, rather than a view because the only way to do it is via dynamic SQL. For the first part, it would be something along the lines of :


declare @sql Varchar(Max) = (
 select  
  string_agg('Select Column1, Column2 From ' + schema_name(schema_id)+'.'+name, ' UNION ALL ')
 from sys.tables where name like 'knowntable%'
)

Exec(@SQL)

Note for brevity I've used the new string_agg function in there, but you can use your favourite string aggregator function to accomplish the same thing on downlevel platforms.

Extending that to handle the later cases of tables in the same schema but with different column names is possible, but you'd need more details on how you'd know which columns map to which in the result set. If it's purely based on order then you can probably use sys.columns to get the names in a similar fashion.

vk1.softcomp Old Hand Points: 303 More actions · Answer 2

Thanks Andy for your reply.
The idea was to have user access to all the data from different tables through a single view and the view shows data dynamically even for newly added schemas.
Regarding the column mapping , that will be hard coded. If 5 tables have same column and structure, then atleast for 1 table it will be hardcoded and remaining 4 will take it via loop. If there are 2 more tables both with different columns and structure, then column mapping will be hard-coded for each one.

Regards,
Vijay

andycadley SSCertifiable Points: 5296 More actions · Answer 3

I think the overall design is highly questionable, if you're going to have different columns hard-coded then every time you add a table you're going to end up having to modify the "view" anyway so there is little to be gained over just hardcoding the entire thing.

If you really want to go down this route, it has to be a stored procedure (it cannot be done in a view or table function) and would look something along the lines of:

Declare @sql Varchar(Max) = '';

With KnownTables As

(
Select
        schema_name(schema_id) As SchemaName,
   'Column1,Column2' As Cols,
name As TableName
From sys.tables
Where name like 'KnownTable%'
),
KnownSchemas As
(
Select
schema_name(schema_id) As SchemaName,
S.TableName,
S.Cols
   From (Values
   ('table3','columnC,columnD'),
('table6','columnA,columnB')
   ) S(TableName, Cols)
   Join sys.tables T On Schema_Name(schema_id) = 'KnownSchema'
And T.name = S.TableName
),
AllTables As
(
Select SchemaName, TableName, Cols From KnownTables
Union All
Select SchemaName, TableName, Cols From KnownSchemas
)
Select
@sql=String_Agg('Select ' + Cols + ' From ' + SchemaName + '.' + TableName, ' Union All ')
From AllTables
Exec(@SQL)

If I were you though, I'd seriously re-think the whole design and preferably put everything into one single table with another column to distinguish whatever attribute of the data it is you're currently using different tables to demark. This will work out a lot less painful to use as a design in the long run.

vk1.softcomp Old Hand Points: 303 More actions · Answer 4

Thanks Andy for your reply.
I will try to simply my question: Below are 5 different tables which user access. Now aim is to have a single view with all combined data from different tables so that user doesnt have to query so many different tables. Mapping is not an issue.
NOte: I have limited the num of rows to only 3. Also number of columns can be more.

1	SCHEMANAME:SchemaX, TABLENAME:TAB_G
	UID	COL1	COl2	TEXT
	1	127	some textA	Comments1
	2	453	some textB	Comments2
	3	234	some textC	Comments3

2	SCHEMANAME:SchemaY, TABLENAME:TAB_G
	UID	COL1	COl2	TEXT
	101	11	some textX	Comments44
	102	22	some textY	Comments5
	103	57	some textZ	Comments62

3	SCHEMANAME:SchemaZ, TABLENAME:TAB_G
	UID	COL1	COl2	TEXT
	21	48	some text4	Comments17
	22	23	some text5	Comments80
	23	10	some text6	Comments9

4	SCHEMANAME:SchemaM, TABLENAME:TAB_MXM
	COLID	COLX	COLY	COLZ	COLN	COLM	TEXTCOL
	1301	txtP	9XUN	datetime	1001	some text	CommentsX
	1302	txtQ	PS8J	date	1010	txt	CommentsY
	1303	txtR	LKS7	som date	3367	text	Comments22

5	SCHEMANAME:SchemaM, TABLENAME:TAB_SBR
	MODID	COLA	COLB	COLC	COLH	COLP	COLV	COLS	COMMENTS
	91	121	text33	dateval1	1001	some text	Y	0	CommentsA
	92	465	text12	dateval2	1010	txt	N	1	Comments0
	93	837	text64	dateval3	3367	text	N	1	CommentsM

	Expected View to have following structure
	TYPE	ID	DETAILS	MESSAGE
	SchemaX-TAB_G	1	COL1=127; COL2=some textA	Comments1
	SchemaX-TAB_G	2	COL1=453; COL2=some textB	Comments2
	SchemaX-TAB_G	3	COL1=234; COl2=some textC	Comments3
	SchemaY-TAB_G	101	COl1=11; COl2=some textX	Comments44
	SchemaY-TAB_G	102	COL1=22; COL2=some textY	Comments5
	SchemaY-TAB_G	103	COL1=57; COL2=some textZ	Comments62
	SchemaZ-TAB_G	21	COL1=48; COL2=some text4	Comments17
	SchemaZ-TAB_G	22	COL1=23; COL2=some text5	Comments80
	SchemaZ-TAB_G	23	COL1=10; COL2=some text6	Comments9
	SchemaM-TAB_MXM	1301	COLY=9XUN; COLN=1001	CommentsX
	SchemaM-TAB_MXM	1302	COLY=PS8J; COLN=1010	CommentsY
	SchemaM-TAB_MXM	1303	COLY=LKS7; COLN=3367	Comments22
	SchemaM-TAB_SBR	91	COLA=121; COLV=Y	CommentsA
	SchemaM-TAB_SBR	92	COLA=465; COLV=N	Comments0
	SchemaM-TAB_SBR	93	COLA=837; COLV=N	CommentsM

If there would have been only 5 tables , then I would have used 4 UNION ALL to combine them with all necessary mapping done easily.

The only issue is Table TAB_G can be in 3 or more different schemas and hence question how many UNION_ALL to use for them and how to use it?
Table 4 and 5 are fixed and wont change. All their mappings can be easily taken care.

-Thanks

andycadley SSCertifiable Points: 5296 More actions · Answer 5

vk1.softcomp - Sunday, July 22, 2018 4:25 PM
The only issue is Table TAB_G can be in 3 or more different schemas and hence question how many UNION_ALL to use for them and how to use it?

The key thing is that there is absolutely no way to do this in a straight view. Either you have to guarantee that TAB_G will always exist (even if the contents are empty) in all the schemas you want it in - in which case you can have a view and it becomes a bigger version of the UNION ALL - or you have to resort to dynamic SQL to build up a list of the tables and combine them on demand, which means you have to go via a stored procedure.

Note that an alternate design would be to have a single TAB_G table with a column that contains data that distinguishes between them in the way you're currently using SchemaX/SchemaY/SchemaZ etc

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

vk1.softcomp - Sunday, July 22, 2018 4:25 PM
Thanks Andy for your reply.
I will try to simply my question: Below are 5 different tables which user access. Now aim is to have a single view with all combined data from different tables so that user doesnt have to query so many different tables. Mapping is not an issue.
NOte: I have limited the num of rows to only 3. Also number of columns can be more.

1     SCHEMANAME:SchemaX, TABLENAME:TAB_G
UID COL1 COl2 TEXT
1 127 some textA Comments1
2 453 some textB Comments2
3 234 some textC Comments3
2     SCHEMANAME:SchemaY, TABLENAME:TAB_G
UID COL1 COl2 TEXT
101 11 some textX Comments44
102 22 some textY Comments5
103 57 some textZ Comments62
3     SCHEMANAME:SchemaZ, TABLENAME:TAB_G
UID COL1 COl2 TEXT
21 48 some text4 Comments17
22 23 some text5 Comments80
23 10 some text6 Comments9
4    SCHEMANAME:SchemaM, TABLENAME:TAB_MXM
COLID COLX COLY COLZ COLN COLM TEXTCOL
1301 txtP 9XUN datetime 1001 some text CommentsX
1302 txtQ PS8J date 1010 txt CommentsY
1303 txtR LKS7 som date 3367 text Comments22
5    SCHEMANAME:SchemaM, TABLENAME:TAB_SBR
MODID COLA COLB COLC COLH COLP COLV COLS COMMENTS
91 121 text33 dateval1 1001 some text Y 0 CommentsA
92 465 text12 dateval2 1010 txt N 1 Comments0
93 837 text64 dateval3 3367 text N 1 CommentsM
Expected View to have following structure
TYPE ID DETAILS MESSAGE
SchemaX-TAB_G 1 COL1=127; COL2=some textA Comments1
SchemaX-TAB_G 2 COL1=453; COL2=some textB Comments2
SchemaX-TAB_G 3 COL1=234; COl2=some textC Comments3
SchemaY-TAB_G 101 COl1=11; COl2=some textX Comments44
SchemaY-TAB_G 102 COL1=22; COL2=some textY Comments5
SchemaY-TAB_G 103 COL1=57; COL2=some textZ Comments62
SchemaZ-TAB_G 21 COL1=48; COL2=some text4 Comments17
SchemaZ-TAB_G 22 COL1=23; COL2=some text5 Comments80
SchemaZ-TAB_G 23 COL1=10; COL2=some text6 Comments9
SchemaM-TAB_MXM 1301 COLY=9XUN; COLN=1001 CommentsX
SchemaM-TAB_MXM 1302 COLY=PS8J; COLN=1010 CommentsY
SchemaM-TAB_MXM 1303 COLY=LKS7; COLN=3367 Comments22
SchemaM-TAB_SBR 91 COLA=121; COLV=Y CommentsA
SchemaM-TAB_SBR 92 COLA=465; COLV=N Comments0
SchemaM-TAB_SBR 93 COLA=837; COLV=N CommentsM
If there would have been only 5 tables , then I would have used 4 UNION ALL to combine them with all necessary mapping done easily.
The only issue is Table TAB_G can be in 3 or more different schemas and hence question how many UNION_ALL to use for them and how to use it?
Table 4 and 5 are fixed and wont change. All their mappings can be easily taken care.
-Thanks

Can't use dynamic SQL in this manner, and to UNION ALL the tables together requires that all tables have the same number of columns and the data type for the columns match.

sgmunson SSC Guru Points: 110640 More actions · Answer 7

I don;t recall if SQL 2008 supports DDL triggers or not. If it does, you could perhaps have a DDL trigger detect the current list of tables of that name and re-establish the view in the process. That trigger would detect any new TABG tables or any dropped TABG tables and just re-establish the view using dynamic SQL every time it sees a change for that table name. This might be the only effective way to do what you WANT to do, but it might not be a terribly good idea...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

vk1.softcomp Old Hand Points: 303 More actions · Answer 8

Is it not possible that..
...inside a View definition, we have a select statement that gets all the new SCHEMA names for a unique known table name..(This unique table, say ABC, will be there across all Schemas, that is, each schema must and will have this unique table ABC)
?
if this is possible then my job is done... (Table is not going to get dropped)

Once I have the schema names , then I can define the view structure from all the different tables.. (Mapping to a new structure from old table structures is not a problem...)

-VK

sgmunson SSC Guru Points: 110640 More actions · Answer 9

vk1.softcomp - Friday, July 27, 2018 10:22 AM
Is it not possible that..
...inside a View definition, we have a select statement that gets all the new SCHEMA names for a unique known table name..(This unique table, say ABC, will be there across all Schemas, that is, each schema must and will have this unique table ABC)
?
if this is possible then my job is done... (Table is not going to get dropped)
Once I have the schema names , then I can define the view structure from all the different tables.. (Mapping to a new structure from old table structures is not a problem...)
-VK

No, it's NOT possible, because views do not allow dynamic SQL, and dynamic SQL is the only possible way to use that technique.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)