0

I have a database that contains two or more schema's. Each schema contains several views.

In schema ALPHA we find the following views:

  • vw_a_schools
  • vw_a_classes
  • vw_a_teachers

In schema BETA we find the following views:

  • vw_b_schools
  • vw_b_classes
  • vw_b_automobiles

What I am looking for is a procedure that will create a schema ALLTOGETHERNOW that unions all views with the same name. So schema ALLTOGETHERNOW will look like this:

  • vw_schools = (select * from vw_a_schools UNION ALL select * from vw_b_schools)
  • vw_classes = (select * from vw_a_classes UNION ALL select * from vw_b_classes)
  • vw_teachers = (select * from vw_a_teachers)
  • vw_automobiles = (select * from vw_b_automobiles)

The following apply:

  • the amount of schemas is not known beforehand. If necessary though we can make a table that lists all the schema's to be joined
  • the amount of views is not known beforehand.
  • Views always look like vw_<something>_viewname. We do know for sure that views with the same name have the same columns.

SO the idea is to somehow loopt through the schemas, collect all views, substract the suffix (the vw_<something> part, then create a statement that will create the view that unions all the views.

I have been playing with information schemas and such and can create a list with viewnames. But there I hit a dead end....

Henrov
  • 1,610
  • 1
  • 24
  • 52
  • Not quite your question, but you may get some hints from [Create a UNION ALL query when the columns are in different order](https://stackoverflow.com/questions/61232740/create-a-union-all-query-when-the-columns-are-in-different-order/61237884#61237884). – HABO Sep 18 '20 at 16:33
  • This, in truth, seems more like the *real* problem is you have a design flaw. Is the name of the schema being used here to infer data (for example the name of the school)? – Thom A Sep 24 '20 at 19:42
  • The schemas in this specific example could be regions. There is a requirement to keep the regions in separate schema's. Some people are only allowed to see their own region, others are allowed to see all regions and the combined data of the regions. – Henrov Sep 25 '20 at 11:01
  • Why not just implement row level security then? That would be a far better solution over repeating objects in different schemas. The design smells like an [XY Problem](http://xyproblem.info). – Thom A Sep 25 '20 at 11:53
  • Schools are just an example. There are other requirements that are in play, which I do not want to go into at this time. Looking at other requirements this is the best solution as discussed with several of my colleagues (old and curent) – Henrov Sep 29 '20 at 08:49

1 Answers1

2

"Automagically" - there is no magic, just dynamic SQL that reads metadata and generate single output query:

DECLARE @create_view_sql NVARCHAR(MAX);

WITH vw(t) AS (
   SELECT DISTINCT RIGHT(TABLE_NAME, LEN(TABLE_NAME)-5) --assumption prefix has 4 chars
   FROM INFORMATION_SCHEMA.VIEWS
   WHERE TABLE_SCHEMA NOT IN ('ALTOGETHER', 'dbo')
)
SELECT @create_view_sql = STRING_AGG(s.sql, CHAR(13)) WITHIN GROUP(ORDER BY vw.t)
FROM vw
CROSS APPLY(
SELECT  N'EXEC(''CREATE OR ALTER VIEW ALTOGETHER.' + QUOTENAME(N'vw_' + vw.t) + ' AS ' +  
       STUFF(STRING_AGG(FORMATMESSAGE('UNION ALL SELECT * FROM %s.%s'
     ,QUOTENAME(TABLE_SCHEMA), QUOTENAME(TABLE_NAME))
    , CHAR(13)) WITHIN GROUP(ORDER BY TABLE_NAME), 1,10,'')
    + N''');'
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME LIKE 'vw_[a-z]_' + vw.t
  AND TABLE_SCHEMA NOT IN ('ALTOGETHER', 'dbo')
)s(sql);

PRINT @create_view_sql;

EXEC sp_executesql @create_view_sql;

It could be enclosed within stored procedure and run in a scheduled manner.

db<>fiddle.com demo

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275