Tweaked @Andriy M's solution which helped me.

Our table column names had got alphanumeric characters that started with numeric value and so the query failed for certain tables. So added square brackets around the column names to @Andriy M's solution.

The modified query goes as below,

DECLARE @table varchar(100), @sql varchar(max);
SET @table = 'some table name';

SELECT
  @sql = COALESCE(@sql + ', ', '') + ColumnExpression
FROM (
  SELECT
    ColumnExpression =
      'CASE COUNT(DISTINCT [' + COLUMN_NAME + ']) ' +
      'WHEN COUNT(1) THEN ''UNIQUE'' ' +
      'WHEN COUNT(1) - 1 THEN ' +
        'CASE COUNT(DISTINCT [' + COLUMN_NAME + ']) ' +
        'WHEN COUNT([' + COLUMN_NAME + ']) THEN ''UNIQUE WITH SINGLE NULL'' ' +
        'ELSE '''' ' +
        'END ' +
      'WHEN COUNT([' + COLUMN_NAME + ']) THEN ''UNIQUE with NULLs'' ' +
      'ELSE '''' ' +
      'END AS [' + COLUMN_NAME +']'
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @table
) s

SET @sql = 'SELECT ' + @sql + ' FROM ' + @table;
PRINT @sql;  /* in case you still want to have a look at the resulting query */
EXEC(@sql);