-- =============================================
-- Description:  a dbo sémában lévő egyedi view-k frissítése az intézményi sémákba
-- =============================================

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('dbo.sp_Global_CreateSchemaAdvancedViews') IS NOT NULL BEGIN
  DROP PROCEDURE [dbo].[sp_Global_CreateSchemaAdvancedViews]
END
GO

CREATE PROCEDURE [dbo].[sp_Global_CreateSchemaAdvancedViews]
  @pSchemaName NVARCHAR(100), -- pl: 'KR_BEDO_Schema' | NULL esetén az összes sémán végigmegy
  @pViewNames XML             -- pl: '<ViewNames><ViewName>A_VIEW_NEVE</ViewName></ViewNames>' | NULL esetén az összes egyedi view-n végigmegy
AS
BEGIN

  DECLARE @objectId INT
  DECLARE @viewName NVARCHAR(4000)
  DECLARE @viewDefinition NVARCHAR(MAX)
  DECLARE @schemaName NVARCHAR(100)
  DECLARE @deleteViewSQL NVARCHAR(4000)
  DECLARE @createViewSQL NVARCHAR(MAX)

  DECLARE @schemaNames CURSOR
  IF @pSchemaName IS NOT NULL 
    SET @schemaNames = CURSOR LOCAL FOR
      SELECT @pSchemaName
  ELSE
    SET @schemaNames = CURSOR LOCAL FOR
      SELECT name FROM sys.schemas 
      WHERE principal_id = 1 AND name LIKE 'KR[_]%[_]Schema'
      ORDER BY name

  DECLARE @views CURSOR
  IF @pViewNames IS NOT NULL 
    SET @views = CURSOR LOCAL FOR
      SELECT v.object_id, v.name, m.Definition
      FROM sys.views v
        INNER JOIN sys.sql_modules m ON v.object_id = m.object_id
        INNER JOIN sys.schemas s ON s.schema_id = v.schema_id
      WHERE 
        s.name ='dbo' AND v.name NOT LIKE '%_OSSZES'
        AND v.name IN (SELECT DISTINCT ViewName.value('(.)[1]', 'varchar(100)') FROM @pViewNames.nodes('ViewNames/ViewName') AS ViewNames(ViewName))
      ORDER BY v.name
  ELSE
    SET @views = CURSOR LOCAL FOR
      SELECT v.object_id, v.name, m.Definition
      FROM sys.views v
        INNER JOIN sys.sql_modules m ON v.object_id = m.object_id
        INNER JOIN sys.schemas s ON s.schema_id = v.schema_id
      WHERE s.name ='dbo' AND v.name NOT LIKE '%_OSSZES'
      ORDER BY v.name

  OPEN @views 
  FETCH NEXT FROM @views INTO @objectId, @viewName, @viewDefinition

  WHILE @@FETCH_STATUS = 0 BEGIN
    IF OBJECT_ID(@objectId) IS NULL BEGIN
      SET @viewDefinition = REPLACE(@viewDefinition, 'dbo.','')   
      SET @viewDefinition = REPLACE(@viewDefinition, '[dbo].','')      

      OPEN @schemaNames
      FETCH NEXT FROM @schemaNames INTO @schemaName
    
      WHILE @@FETCH_STATUS = 0 BEGIN
        SET @deleteViewSQL = 'IF OBJECT_ID(''[' + @schemaName + '].' +  @viewName + ''') IS NOT NULL
          DROP VIEW [' + @schemaName + '].' + @viewName

        SET @createViewSQL = REPLACE(@viewDefinition, 'CREATE VIEW ','CREATE VIEW [' + @schemaName + '].')
      
        PRINT CAST(@objectId AS VARCHAR(10)) + '  ' + @viewName + '   ' + @schemaName
          
        EXEC sp_executesql @deleteViewSQL
        EXEC sp_executesql @createViewSQL
    
        FETCH NEXT FROM @schemaNames INTO @schemaName
      END

      CLOSE @schemaNames
    END

    FETCH NEXT FROM @views INTO @objectId, @viewName, @viewDefinition
  END             

  CLOSE @views
  DEALLOCATE @views
  DEALLOCATE @schemaNames

END