i have the following problem. I inherited a software that uses a database prefix for every customer. All tables have the same structure and columns. For a data migration to new version i want to union all these tables and set a customer foreign key instead and get rid of all the subtables. i'm looking for a way to create a view for this task because i also want to stay backwards compatible for now. I found this dynamic query which seems to do what i want but i can't execute on my mysql server. I assume it was written for another sql server.
The table name structure is (about 80 customer tables): customer1_faxe customer2_faxe customer3_faxe customer4_faxe ...
How would you approach this problem?
DECLARE @SelectClause VARCHAR(100) = 'SELECT *' ,@Query VARCHAR(1000) = '' SELECT @Query = @Query + @SelectClause + ' FROM ' + TABLE_NAME + ' UNION ALL ' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%_faxe' SELECT @Query = LEFT(@Query, LEN(@Query) - LEN(' UNION ALL ')) EXEC (@Query)