DROP PROCEDURE IF EXISTS dev.uspDropSchemaViews
GO

CREATE PROCEDURE dev.uspDropSchemaViews
   @pViewList nvarchar(max) -- Vesszővel elválasztott SP és FN lista (akár vegyesen is), NULL esetén az összeset frissíti
  ,@pSchemaName nvarchar(max) = NULL -- Schema neve, amit frissíteni kell, NULL esetén az összes schemán végigmegy
AS
BEGIN
  SET NOCOUNT ON
  
  DECLARE 
     @sql nvarchar(max) = '';

  IF @pSchemaName IS NULL AND @pViewList IS NULL BEGIN
    RETURN
  END

  DECLARE @View TABLE (
     Id int IDENTITY(1, 1)
    ,ViewName nvarchar(255)
  )

  INSERT INTO @View (ViewName)
  SELECT LTRIM(RTRIM(s.value))
  FROM STRING_SPLIT (@pViewList, N',') s
  UNION ALL
  SELECT LTRIM(RTRIM(s.value)) + '_OSSZES'
  FROM STRING_SPLIT (@pViewList, N',') s


  IF @pSchemaName IS NULL BEGIN

    SELECT @sql += 'DROP VIEW IF EXISTS [' + SCHEMA_NAME(v.schema_id) + '].[' + v.name + ']' + char(13) + char(10) 
    FROM sys.views v
      INNER JOIN @View x ON x.ViewName COLLATE HUNGARIAN_CI_AS = v.name COLLATE HUNGARIAN_CI_AS
    ORDER BY x.Id
  END

  IF @pViewList IS NULL BEGIN

    SELECT @sql += 'DROP VIEW IF EXISTS [' + SCHEMA_NAME(v.schema_id) + '].[' + v.name + ']' + char(13) + char(10) 
    FROM sys.views v
    WHERE SCHEMA_NAME(v.schema_id) = @pSchemaName

  END  

  IF @pViewList IS NULL AND @pSchemaName IS NOT NULL BEGIN

    SELECT @sql += 'DROP VIEW IF EXISTS [' + SCHEMA_NAME(v.schema_id) + '].[' + v.name + ']' + char(13) + char(10) 
    FROM sys.views v
      INNER JOIN @View x ON x.ViewName COLLATE HUNGARIAN_CI_AS = v.name COLLATE HUNGARIAN_CI_AS
    WHERE SCHEMA_NAME(v.schema_id) = @pSchemaName

  END

  EXEC sp_executesql @sql

END
GO