CREATE_OR_DROP_CLUSTEREDINDEX

create procedure [BBDW].[CREATE_OR_DROP_CLUSTEREDINDEX]
  @SCHEMA nvarchar(128),
  @TABLENAME nvarchar(128),
  @COLUMNNAME nvarchar(128),
  @CREATE_OR_DROP bit --1 to create, 0 to drop
as
  set nocount on;

  declare @SQL nvarchar(max)

  if @CREATE_OR_DROP = 0 and exists (select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_SCHEMA = @SCHEMA and TABLE_NAME = @TABLENAME and CONSTRAINT_NAME = 'PK_' + @TABLENAME)
  begin

    set @SQL = 
      'if not exists(select top 1 1 from ' + @SCHEMA + '.[' + @TABLENAME + '])' + char(13) +
      'begin' + char(13) + 
      '  alter table ' + @SCHEMA + '.[' + @TABLENAME + '] drop constraint [PK_' + @TABLENAME + '];' + char(13) +
      'end';

    exec sp_executesql @SQL;

  end

  else if @CREATE_OR_DROP = 1  and not exists (select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_SCHEMA = @SCHEMA and TABLE_NAME = @TABLENAME and CONSTRAINT_NAME = 'PK_' + @TABLENAME)
  begin

    set @SQL = 'alter table ' + @SCHEMA + '.[' + @TABLENAME + '] add constraint [PK_' + @TABLENAME + '] primary key clustered ([' + @COLUMNNAME + '] asc);';
    exec sp_executesql @SQL;

  end