﻿DECLARE SchemaCursor CURSOR FOR 
SELECT DISTINCT 
   i.C_AZONOSITO AS IntezmenyAzonosito
  ,i.ID AS IntezmenyId
  ,t2.ID AS TanevId
FROM T_TANEV t
  INNER JOIN T_INTEZMENY i ON i.ID = t.C_INTEZMENYID
  INNER JOIN T_TANEV t2 ON t2.C_INTEZMENYID = t.C_INTEZMENYID AND t2.TOROLT = 'F' AND t2.C_AKTIV = 'T'
WHERE t.TOROLT = 'T'
  AND i.TOROLT = 'F'


DECLARE 
   @schemaName nvarchar(100)
  ,@intezmenyAzonosito nvarchar(100)
  ,@intezmenyId int
  ,@tanevID int
  ,@sql nvarchar(max)
  
OPEN SchemaCursor
FETCH NEXT FROM SchemaCursor INTO @intezmenyAzonosito, @intezmenyId, @tanevID

WHILE @@FETCH_STATUS = 0 BEGIN
  SET @sql = ''
  SET @schemaName = 'KR_' + @intezmenyAzonosito + '_Schema'

  SELECT @sql += 'DROP VIEW [' + @schemaName + '].' + name + char(13) + char(10) + ';' 
  FROM sys.views 
  WHERE schema_id = SCHEMA_ID(@schemaName)

  EXEC sp_executesql @sql

  EXEC [dbo].[sp_Global_CreateSchemaViews] 		
     @IntezmenyId = @intezmenyId
    ,@IntezmenyAzonosito = @intezmenyAzonosito
    ,@TanevId = @tanevID
  
  FETCH NEXT FROM SchemaCursor INTO @intezmenyAzonosito, @intezmenyId, @tanevID
END

CLOSE SchemaCursor
DEALLOCATE SchemaCursor
GO
