t-sqlazure-synapse

I want to create View from the system tables


I wanted to create a view from the system tables, I could output the syntax which I want to run but how could I run that output command? I created the following query which output the syntax to create the table

SELECT

'CREATE VIEW [Uti_John].[Vw_' + x.table_name +']' +
' AS SELECT * FROM [' + x.schema_name + '].[' + x.table_name +']' 
FROM
(
SELECT SCHEMA_NAME(schema_id) AS schema_name  
       ,name AS table_name   
FROM sys.tables 
WHERE name like 'T%' 
and SCHEMA_NAME(schema_id) = 'dbo'
) x

Output

CREATE VIEW [Uti_John].[Vw_TNAME] AS SELECT * FROM [dbo].[TNAME]
CREATE VIEW [Uti_John].[Vw_TClient] AS SELECT * FROM [dbo].[TClient]
CREATE VIEW [Uti_John].[Vw_TClaim] AS SELECT * FROM [dbo].[TTClaim]
CREATE VIEW [Uti_John].[Vw_TInsurance] AS SELECT * FROM [dbo].[TTInsurance]
CREATE VIEW [Uti_John].[Vw_Ttest] AS SELECT * FROM [dbo].[Ttest]

copying manually one by one output of this creates the view but how could I run this output in a loop and create automatically?


Solution

  • You can use the sys.views view to check if the view already exists.

    DECLARE  @SQL NVARCHAR(max)='*';
    
    WHILE @SQL IS NOT NULL
    BEGIN 
    
        SET @SQL = NULL;
    
        SELECT @SQL = 
        'CREATE VIEW [Uti_John].[Vw_' + x.table_name +']' +
        ' AS SELECT * FROM [' + x.schema_name + '].[' + x.table_name +']' 
        FROM
        (
            SELECT SCHEMA_NAME(schema_id) AS schema_name  
                   ,t.name AS table_name   
            FROM sys.tables t
            WHERE t.name like 't%' 
            and SCHEMA_NAME(t.schema_id) = 'dbo'
            AND NOT EXISTS (    SELECT * 
                                FROM sys.views v 
                                WHERE v.name = 'Vw_' + t.name 
                                AND OBJECT_SCHEMA_NAME(v.object_id) = 'Uti_John'
                            )
        ) x
    
        EXEC sp_executesql @SQL;
    
        PRINT @SQL;
    
    END ;