create view on a reporting database by searching for specific views on many different DWH database

[csharpenthusiast007]

Hi, I am working on creating a view dynamically from many different database. For instance search through all the user database and look for view with prefix "view_Dim_" and create the view on ReportDB Database. Please give me ideas on how to accomplish this.

DECLARE @Database SYSNAME

SET @Database = 'ReportDB'

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'USE '+ QUOTENAME(@Database ) +';
EXEC(''
CREATE VIEW [dbo].[MyTable] AS
SELECT 1 AS [Col]
'')'

EXEC (@SQL)

Thank you.

 

[johnherman]

Well, you need to either provide a list of table.column choices by querying the system tables, or ask the person running the query to supply the table and columns. Then, you need to get/validate the join conditions between the tables involved to avoid a Cartesian product query within the view. Finally, you need to GRANT SELECT on the table to the appropriate group, role, or user(s). Just a few thoughts, others may help you better define the actual syntax.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity