What are the maximum number of Columns in a table in SQL Server?
SQL Server supports a maximum of 1,024 Columns in a nonwide table.
SQL Server supports a maximum of 30,000 Columns in a wide table.
SQL Server supports a maximum of 1,024 Columns in a base table.

Max Capacity For	SQL Server 2012	SQL Server 2008 R2	SQL Server 2008	SQL Server 2005
maximum number of Columns in a nonwide table in SQL Server	1,024 Columns	1,024 Columns	1,024 Columns	–
maximum number of Columns in a wide table in SQL Server	30,000 Columns	30,000 Columns	30,000 Columns	–
maximum number of Columns in a base table in SQL Server	–	–	–	1,024

Now if you want to test the maximum number of columns limitation in a table you can use the below test script.

Test Script:
[sql]
–DROP TABLE TestCreateMaximumColumnsTable
DECLARE @i INT = 2
DECLARE @SQLTABLE VARCHAR(MAX)

SET @SQLTABLE = ‘CREATE TABLE TestCreateMaximumColumnsTable(COLUMN1 BIT)’

EXEC(@SQLTABLE)
WHILE @i <= 1025
BEGIN
SELECT @SQLTABLE = ‘ALTER TABLE TestCreateMaximumColumnsTable
ADD COLUMN’+CAST(@i AS VARCHAR(5))+’ BIT’
EXEC(@SQLTABLE)
SET @i = @i + 1
END
[/sql]

When the script tries to create 1025th column you get the below error:

Msg 1702, Level 16, State 1, Line 1
CREATE TABLE failed because column ‘COLUMN1025’ in table ‘TestCreateMaximumColumnsTable’ exceeds the maximum of 1024 columns.

http://msdn.microsoft.com/en-us/library/ms143432.aspx

Reference : Devi Prasad (sqlserverlearner.com)