title: Delete multiple columns with constraints from different tables
body: <p>I'm trying to delete different columns from different tables, using T-SQL.</p>
  
  <p>I made this code, following some tips that I found here on SO</p>
  
  <pre><code>/* Declaration of variable */
  declare @tablename nvarchar(200)
  declare @colname nvarchar(200)
  declare @default sysname, @sql nvarchar(max)
  
  /* **** Modifications in TITULORECEBERFATURA_ITEMS**** */
  /* Drop column NRSEQORDEM */
  set @tablename = 'TITULORECEBERFATURA_ITEMS'
  set @colname = 'NRSEQORDEM'
  
  select @default = CONSTRAINT_NAME
  FROM 
     INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 
  WHERE 
     TABLE_NAME = @tablename 
     AND COLUMN_NAME = @colname
  
     set @sql = N'alter table ' + @tablename + ' drop constraint ' + @default
  exec sp_executesql @sql
  
  set @sql = N'alter table ' + @tablename + ' drop column ' + @colname
  exec sp_executesql @sql
  </code></pre>
  
  <p>The above code works without any kind of problem. But when I add other code below it, occurs an error.</p>
  
  <pre><code>/* Declaration of variable */
  declare @tablename nvarchar(200)
  declare @colname nvarchar(200)
  declare @default sysname, @sql nvarchar(max)
  
  /* **** Modifications in TITULORECEBERFATURA_ITEMS**** */
  /* Drop column NRSEQORDEM */
  set @tablename = 'TITULORECEBERFATURA_ITEMS'
  set @colname = 'NRSEQORDEM
  
  select @default = CONSTRAINT_NAME
  FROM 
     INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 
  WHERE 
     TABLE_NAME = @tablename 
     AND COLUMN_NAME = @colname
  
     set @sql = N'alter table ' + @tablename + ' drop constraint ' + @default
  exec sp_executesql @sql
  
  set @sql = N'alter table ' + @tablename + ' drop column ' + @colname
  exec sp_executesql @sql
  
  /* **** Modifications in EMAILCONTAS **** */
  /* Drop column NRSEQOPERADORA */
  set @tablename = 'EMAILCONTAS'
  set @colname = 'NRSEQOPERADORA'
  
  select @default = CONSTRAINT_NAME
  FROM 
     INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 
  WHERE 
     TABLE_NAME = @tablename 
     AND COLUMN_NAME = @colname
  
     set @sql = N'alter table ' + @tablename + ' drop constraint ' + @default
  exec sp_executesql @sql
  
  set @sql = N'alter table ' + @tablename + ' drop column ' + @colname
  exec sp_executesql @sql
  </code></pre>
  
  <p>The <code>NRSEQORDEM</code> and his constraint are dropped without problems, But I got this error with the <code>NRSEQOPERADORA</code> and his constraint column</p>
  
  <blockquote>
    <p>Msg 5074, Level 16, State 1, Line 1<br>
    The object 'FK_<em>EMAILCONT</em>_NRSEQ__25276EE5' is dependent on column 'NRSEQOPERADORA'.<br>
    Msg 4922, Level 16, State 9, Line 1<br>
    ALTER TABLE DROP COLUMN NRSEQOPERADORA failed because one or more objects access this column.</p>
  </blockquote>
  
  <p>And the columns <code>NRSEQOPERADORA</code> doesn't drop.</p>
  
  <p>If I use different names for <strong>each</strong> declare, I can do it (but there is not only 2, but lots of those, and is not pretty do it, I'm looking for a "right" way of doing this)</p>
  
  <pre><code>/* Declaration of variable */
      declare @tablename nvarchar(200)
      declare @colname nvarchar(200)
      declare @tablename2 nvarchar(200)
      declare @colname2 nvarchar(200)
      declare @default sysname, @sql nvarchar(max)
  
      /* **** Modifications in TITULORECEBERFATURA_ITEMS**** */
      /* Drop column NRSEQORDEM */
      set @tablename = 'TITULORECEBERFATURA_ITEMS'
      set @colname = 'NRSEQORDEM
  
      select @default = CONSTRAINT_NAME
      FROM 
         INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 
      WHERE 
         TABLE_NAME = @tablename 
         AND COLUMN_NAME = @colname
  
         set @sql = N'alter table ' + @tablename + ' drop constraint ' + @default
      exec sp_executesql @sql
  
      set @sql = N'alter table ' + @tablename + ' drop column ' + @colname
      exec sp_executesql @sql
  
      /* **** Modifications in EMAILCONTAS **** */
      /* Drop column NRSEQOPERADORA */
      set @tablename2 = 'EMAILCONTAS'
      set @colname2 = 'NRSEQOPERADORA'
  
      select @default = CONSTRAINT_NAME
      FROM 
         INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 
      WHERE 
         TABLE_NAME = @tablename2 
         AND COLUMN_NAME = @colname2
  
         set @sql = N'alter table ' + @tablename2 + ' drop constraint ' + @default
      exec sp_executesql @sql
  
      set @sql = N'alter table ' + @tablename2 + ' drop column ' + @colname2
      exec sp_executesql @sql
  </code></pre>
  
  <hr>
  
  <p><strong>EDIT:</strong></p>
  
  <ol>
  <li>The code <strong>works</strong> The constraint is deleted, the column is deleted. (See first code sample)</li>
  <li>If I put a follow script, with the <strong>set</strong> of a different table and column but using the same declare name (@tablename, @colname, @default), the first part of the code <strong>will</strong> drop the constrain and the column without any problem but the second script will show an error message (See the error quote and the second code sample)</li>
  <li>I need to be able to make it more than once at the same time. I can't afford run one query at time, I'm doing a script to execute them all at once. But the Declare part is making things a little harder. If I use a different declare for every query, it's gonna work; But I don't think that this is the right way of doing this. This is why I'm wondering, how could I accomplish this? Or there is <strong>no way</strong> besides the workaround?</li>
  </ol>