In SQL Dynamic view is possible?

Question

Actually I know how to create a view in SQL. But I am stuck with this one case.

One of my databases is creating tables monthly. For example table names are TL202001, TL202002, TL202003, so next month new table will be TL202004. All the tables have the same columns. But difference in data I want to generate a view for all this dynamic tables. But I don't know how to make logic for future tables. Let me know for this case View is possible and let me know your solution.

Please help!

I will explain again clearly,

Example:

My Database name is TAS
In the TAS database, every month a new table is created from a piece of software

For instance:

T_LG202001
T_LG202002
T_LG202003
T_LG202004 etc...

I want to create a view to for all these tables together. It will also need to consider future month's tables.

select * 
from T_LG202001,T_LG202002,T_LG202003.........T_LG202030 etc.

Please let me know. now you are understand. I need to know that View is possible? Also let me know if possible solution.

Dynamic SQL is not allowed in a view, but you can dynamically create a view. Take a peek at https://stackoverflow.com/questions/41807327/sql-dynamic-pivot-table/41807528#41807528 — John Cappelletti, Mar 14 '20 at 15:40
[Dynamic Table Names in Application Code](http://www.sommarskog.se/dynamic_sql.html#dyntablenames) - Attribute as Part of the Table Name/Home-Brewed Partitioning — Lukasz Szozda, Mar 14 '20 at 15:43
Honestly, th fact that you have tables like this sounds like a design flaw; if you can you should be normalising your data. — Thom A, Mar 14 '20 at 15:55
It sounds like your data definition of the monthly table can change from time to time. So this is not just data partition but also version control...I think no one can help unless you can provide what are the possible styles of "logic changes" to the new tables. — COY, Mar 14 '20 at 16:02
Though the duplicate talks about dynamic schema names and not table names, the principle is the same. Still, I agree with @Larnu - This sounds like a design flaw and should be normalized if possible. — Zohar Peled, Mar 14 '20 at 18:15
The traditional approach is to determine when you will no longer be responsible for this view. Create empty tables out to that time and incorporate them into the view. When it fails it will be [SEP](https://en.wikipedia.org/wiki/Somebody_else's_problem). — HABO, Mar 14 '20 at 18:20

score 1 · Answer 1 · answered Mar 14 '20 at 15:55

First thing it is not best design. Dynamic Table Names in Application Code - Attribute as Part of the Table Name/Home-Brewed Partitioning

Workaround only if you cannot rework current setup:

Recreating view every time there is new table or on monthly schedule:

DECLARE @create_view_sql NVARCHAR(MAX);

SELECT @create_view_sql = N'CREATE OR ALTER VIEW my_combined_view 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,'')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'TL2[0-9][0-9][0-9][0-9][0-9]I'
  AND TABLE_TYPE = 'BASE TABLE'
  --AND TABLE_SCHEMA = ...;

PRINT @create_view_sql;

EXEc sp_executesql @create_view_sql;

db<>fiddle demo

In SQL Dynamic view is possible?

1 Answers1