CREATE_OR_DROP_ALL_FK

create procedure [BBDW].[CREATE_OR_DROP_ALL_FK]
@CREATE_OR_DROP bit --1 to create, 0 to drop.
as
  set nocount on;

  declare @SCHEMA nvarchar(255);
  declare @TABLE nvarchar(255);
  declare @COLUMN nvarchar(255);
  declare @FKVALUE nvarchar(255);
  declare @FKNAME nvarchar(255);
  declare @FKcolumn nvarchar(255);
  declare @FKtable nvarchar(255);
  declare @FKschema nvarchar(255);
  declare @SQL as nvarchar(max);

  if @CREATE_OR_DROP is null
    raiserror('@CREATE_OR_DROP must be 1 or 0 in [BBDW].[CREATE_OR_DROP_ALL_FK]',16,10);

  --Grab all the FK columns and the FK metadata 
  declare TABLECOLUMN cursor local fast_forward for
  select 
    s.[name] as [SCHEMA],
    t.[name] as [TABLE], 
    c.[name] as [COLUMN],   
    cast(ep.[value] as nvarchar(255)) as [FKVALUE],    
    'FK_' + t.[name] + '_' + c.[name] as [FKNAME]
  from sys.extended_properties as ep
  inner join sys.tables as t on ep.major_id = t.object_id 
  inner join sys.schemas s on t.schema_id = s.schema_id
  inner join sys.columns as c on ep.major_id = c.object_id and ep.minor_id = c.column_id
  where class = 1 and ep.name = 'FK_REFERENCE' 
  and (c.[name] like '%FACTID' or c.[name] like '%DIMID');

  open TABLECOLUMN;    
  fetch next from TABLECOLUMN into @SCHEMA, @TABLE, @COLUMN, @FKVALUE, @FKNAME;
  --For each FK column, create the FK constraint
  while(@@fetch_status <> -1)
  begin

    --Break up the FK metadata into parts
    set @FKVALUE = replace(replace(@FKVALUE,'[',''), ']','');    
    select
      @FKschema = left(@FKvalue, charindex('.',@FKVALUE) - 1),
      @FKtable = substring(
        @FKVALUE, 
        charindex('.',@FKVALUE) + 1, 
        charindex('.',@FKVALUE, charindex('.',@FKVALUE) + 1) - (charindex('.',@FKVALUE) + 1)
      ),
      @FKcolumn = reverse(left(reverse(@FKVALUE), charIndex('.', reverse(@FKVALUE)) - 1));

    if @CREATE_OR_DROP = 1
    begin      
      --If the FK does not already exist will try to create it
      if not exists(select 1 from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME = @FKNAME and CONSTRAINT_SCHEMA = @SCHEMA)
      begin            
        --Create FK creation script
        set @SQL =  'alter table [' + @SCHEMA + '].[' + @TABLE + ']' + char(13) +
                    'with nocheck add constraint [' + @FKNAME + '] foreign key([' + @COLUMN + '])' + char(13) +
                    'references [' + @FKschema + '].[' + @FKtable + '] ([' + @FKcolumn + ']);';

        begin try                            
          exec sp_executesql @SQL;
        end try
        begin catch        
          --select @SQL
          --Do not want to fail ETL for this
        end catch

      end
    end
    else
    begin
      --If the FK does exist, try to drop it
      if exists(select 1 from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME = @FKNAME and CONSTRAINT_SCHEMA = @SCHEMA)
      begin            
        --Create FK drop script
        set @SQL =  'alter table [' + @SCHEMA + '].[' + @TABLE + '] drop constraint [' + @FKNAME + '];';

        begin try                            
          exec sp_executesql @SQL;
        end try
        begin catch        
          --select @SQL;
          --Do not want to fail ETL for this
        end catch
      end      
    end

    fetch next from TABLECOLUMN into @SCHEMA, @TABLE, @COLUMN, @FKVALUE, @FKNAME;
  end 

  close TABLECOLUMN;
  deallocate TABLECOLUMN;