--- channels-no-groups

UPDATE channel_members
SET deleted = 1
WHERE is_group = 1;

UPDATE channels
SET total_members = (
  SELECT count(*)
  FROM channel_members
  WHERE channel_members.channel_id = channels.id
        AND channel_members.deleted = 0);

-- drop is_group

DECLARE @ObjectName NVARCHAR(100)
SELECT @ObjectName = OBJECT_NAME([default_object_id])
FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID('channel_members') AND [name] = 'is_group'
EXEC ('ALTER TABLE channel_members DROP CONSTRAINT ' + @ObjectName);

ALTER TABLE channel_members
  DROP COLUMN is_group;

-- drop group_id

DECLARE @constraint VARCHAR(100)
SELECT @constraint = KCU.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
  INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
    ON KCU.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
       AND KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
       AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE KCU.TABLE_NAME = 'channel_members' AND KCU.COLUMN_NAME = 'group_id'
EXEC ('ALTER TABLE channel_members drop CONSTRAINT ' + @constraint);

DROP INDEX channel_members.channel_members_group_id_uniq;

ALTER TABLE channel_members
  DROP COLUMN group_id;
