Sometimes you just need to drop a column from all tables where it exists in a specific schema or maybe your entire database. The following script will allow you to do that and will also drop any indexes that use the specified column in order to all the column to be dropped.

Use this one with care.

 

-- Remove a Column from all tables where it exists  
declare @SchemaName varchar(128) = 'demo', -- the schema in which you want to check, provide 'all' for all schemas  
        @ColumName varchar(128) = 'DemoColumn' -- the column name that you want to delete

declare @Table varchar(128), -- variable used to store the name of the table while you iterate over the values of the cursor  
        @Schema varchar(128) -- variable used to store the name of the schema while you iterate over the values of the cursor 

-- Table variable used to store the name of the index(s) (if any) while you iterate over the values of the cursor 
declare @Index table (Ident int identity(1,1),  
                      IndexName varchar(128)) 

-- create the cursor that will have all the tables on which the column exists
declare TablesToUse cursor read_only for  
    select distinct TableName = object_name(object_id),
                    SchemaName = object_schema_name(object_id)
    from sys.columns
    where (object_schema_name(object_id) = @SchemaName or @SchemaName = 'all') and
          name = @ColumName;

open TablesToUse;

fetch next from TablesToUse  
into @Table, @Schema;

begin transaction;

while @@FETCH_STATUS = 0  
begin

    -- clean out the table variable
    delete from @Index;

    -- check to see if the column is part of any indexes
    insert into @Index (IndexName)
    select distinct i.name
    from sys.indexes i
     inner join sys.index_columns ic on i.object_id = ic.object_id and
                                        i.index_id  = ic.index_id 
     inner join sys.columns c on ic.column_id = c.column_id
    where object_schema_name(i.object_id) = @Schema and
          object_name(i.object_id) = @Table and
          c.name = @ColumName

    -- drop all the indexes that the column is part of
    if exists (select 1 from @Index)
    begin
        declare @start int = 1,
                @end int,
                @indexName varchar(128) = '',
                @indexSql nvarchar(4000) = '';

        set @end = (select max(Ident) from @Index);

        while @start <= @end
        begin
            set @indexName = (select IndexName from @Index where Ident = @start);

            set @indexSql = 'drop index [' + @indexName + '] on [' + @Schema + '].[' + @Table + ']';

            exec sp_executesql @indexSql;

            set @start += 1;
        end
    end

    declare @sql nvarchar(4000) = 'alter table [' + @Schema + '].[' + @Table + '] drop column [' + @ColumName + ']';
    exec sp_executesql @sql;

    fetch next from TablesToUse
    into @Table, @Schema;

end

commit transaction;

close TablesToUse;  
deallocate TablesToUse;