DROP PROCEDURE IF EXISTS dev.uspDropSchema
GO

CREATE PROCEDURE dev.uspDropSchema
   @pSchemaName nvarchar(max)
AS
BEGIN
  SET NOCOUNT ON
  
  DECLARE 
     @sql nvarchar(max) = '';

  SELECT @sql += 'DROP USER IF EXISTS [' + REPLACE(@pSchemaName, '_schema', '_user]') + char(13) + char(10) 
  EXEC sp_executesql @sql

  SET @sql = '';
  SELECT @sql += '
  IF EXISTS (SELECT 1 FROM master.sys.server_principals WHERE name = ''' + REPLACE(@pSchemaName, '_schema', '_user''') + ') BEGIN
    DROP LOGIN [' + REPLACE(@pSchemaName, '_schema', '_user]') + '
  END'
  EXEC sp_executesql @sql

  SET @sql = '';
  SELECT @sql += 'DROP SCHEMA IF EXISTS [' + @pSchemaName + ']' + char(13) + char(10) 
  EXEC sp_executesql @sql

END
GO