use sample
declare @SQL = nvarchar(max)
declare @tblnm = nvarchar(64)
set @SQL = 'alter table ['+@tblnm+'drop column Active'
select @tblnm = from information_schema.columns 
where column_name = 'active'

exec sp_msforeachtable
'alter table ''?'' drop column Active'

use MyDatabase
set nocount on
select 'use MyDatabase' 
SELECT 'ALTER TABLE ' + o.name + ' ADD PROMO_ROLLOFF_DATE varchar(25) NULL  
 go '
 FROM (select name from sys.objects where (name   like 'DLRT%')
			and name <> 'DLTest' and schema_id = 1) o 
order by o.name

DECLARE @SQL nvarchar(max) = ''
SELECT @SQL = @SQL + CHAR(10) + '
     BEGIN TRY 
        ALTER TABLE [' + TABLE_NAME + ']
        DROP COLUMN Active  
     END TRY 
     BEGIN CATCH 
        PRINT ''' + TABLE_NAME + ''' 
     END CATCH;'
FROM information_schema.columns 
WHERE COLUMN_NAME= 'Active'
PRINT @SQL