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;