I have a large table in SQL Server 2008 R2 and it contains many columns and millions of rows.
There are columns for which all rows have identical values.
For example
col1 col2 col3 col4 col5..... a b c 1 null a d e 1 null a f g 1 null a h I 1 null I want to remove those columns such as col1, col4, col5 and more columns like those.
I do know drop column, I guess I just don't know how to select multiple columns in these case
How could I proceed in this case ?
Thanks very much
You can specify multiple columns in one statement:
alter table mytable drop column col4, col5 To see how many values a given column has, you can use count distinct:
Select count(distinct col1), count(distinct col2), count(distinct col3), ... from mytable Here's the outline of how to build the previous query dynamically:
Declare @sql nvarchar(max) = N'select', @tab sysname = 'mytable', -- replace with table name @col sysname, @sep nvarchar(1) Declare col_cursor cursor local fast_forward for select name from sys.columns where object_id = object_id(@tab) open col_cursor fetch next from col_cursor into @col while @@fetch_status = 0 begin set @sql += @sep + N' count(distinct ' + quotename(@col) + N') as ' + quotename(@col) set @sep = N',' fetch next from col_cursor into @col end close col_cursor deallocate col_cursor set @sql += ' from ' + quotename(@tab) exec sp_executesql @sql 
