How to Drop Unused Empty Redundant columns

--this script uses a TABLE called dbo.Campaign. you need TO find AND REPLACE dbo.Campaign WITH your TABLE NAME 

 
CREATE TABLE mytemp

 (

 id BIGINT IDENTITY(1, 1),

 [count] BIGINT,

 columnname VARCHAR(50)

 )


-- TRUNCATE TABLE mytemp

 
DECLARE @columns TABLE

 (

 id BIGINT IDENTITY(1, 1),

 columnname VARCHAR(50)

 )

 
INSERT INTO @columns ( columnname )

 SELECT column_name

 FROM INFORMATION_SCHEMA.columns

 WHERE table_name = 'Campaign'

 
DECLARE @counter BIGINT

DECLARE @columnname VARCHAR(50)

DECLARE @rowcount BIGINT 

DECLARE @emptyrowcount BIGINT

        

SET @counter = 1

SET @rowcount = ( SELECT COUNT(*)

 FROM Campaign

 )

 

WHILE @counter <= ( SELECT MAX(id)

 FROM @columns

 )

 BEGIN

            

 SELECT @columnname = columnname

 FROM @columns

 WHERE id = @counter

 EXECUTE

 ( 'insert into mytemp ([count], columnname ) SELECT count('

 + @columnname + ') as [count], ''' + @columnname

 + ''' as columnname FROM Campaign a where ' + @columnname

 + ' IS not NULL and RTRIM(LTRIM(' + @columnname

 + ')) <> '''' '

 )

           

 SET @emptyrowcount = ( SELECT [COUNT]

 FROM mytemp

 WHERE id = @counter

 )

              

 IF @emptyrowcount = 0 

 BEGIN

 EXECUTE ( '

            ALTER TABLE Campaign

            DROP COLUMN ' + @columnname

 )

            

 END

                

 SET @counter = @counter + 1

 END

DROP TABLE mytemp 



--SELECT * FROM dbo.mytemp 

--select * from campaign