I'm starting to create a dimensional database for BISM development using tables and views from a relational database on the same SQL instance. I've been looking for a way to dynamically generate drop/create/populate tables (with source data types) in the dimensional database from a list of table and view names (stored in a table) in the target warehouse, rather than new CREATE scripts and maintaining truncate/insert procedures (kept in the target dimensional database) when new lookup (dim suitable) tables and/or fields are added in the relational database.
I've read a great answer from Aaron Bertrand to a sightly different post/question Is there a way to generate table create script in TSQL?, and was hoping someone could give me a pointer on how I might adapt it for this purpose. I don't mind doing the research legwork, but the subtlety/sophistication in the code is a bit too advanced for me :)
