What?
Yes you could just run a SELECT DISTINCT query along with collation to make it case-sensitive and/or accent-sensitive but using this stored procedure means I only specify the table and then the columns.


How?
IF OBJECT_ID('usp_ListDistinctValuesAndCounts', 'P') IS NOT NULL
    DROP PROCEDURE [usp_ListDistinctValuesAndCounts];
GO

CREATE PROCEDURE [usp_ListDistinctValuesAndCounts]
(
          @p_SearchTable VARCHAR(max),
          @p_SearchColumns VARCHAR(max),
          @p_UseCollation VARCHAR(max) = 'Latin1_General_CS_AS'
)
AS
/*****************************************************************************************************************
** Author:      Joel Lipman                                                                                     **
** Created On:  26 October 2012                                                                                 **
** Revision:    v1.0                                                                                            **
** Description: Stored Procedure to list distinct values and counts (defaults to case and accent-sensitive).    **
**                                                                                                              **
** Usage:                                                                                                       **
**    EXEC [usp_ListDistinctValuesAndCounts] 'myDB.dbo.myTable', 'myColumn1,myColumn2', 'Latin1_General_CS_AS'; **
**                                                                                                              **
** Return Values : Table of three columns:                                                                      **
**    - myValue:  the distinct values                                                                           **
**    - myColumn: the column this value was found in.                                                           **
**    - myCount: the number of times this value was found in this column.                                       **
**                                                                                                              **
** Link to other SQL Server collations: http://msdn.microsoft.com/en-us/library/ms180175.aspx                   **
**                                                                                                              **
******************************************************************************************************************
** Change History:                                                                                              **
******************************************************************************************************************
** Date:                By:             Rev:        Desc:                                                       **
** ------------------------------------------------------------------------------------------------------------ **
** 26/10/2012           Jlipman         1.0         Created                                                     **
**                                                                                                              **
*****************************************************************************************************************/
BEGIN

        -- Declare some variables to use
        DECLARE
                @ColToProcess varchar(max),
                @SqlToExecute varchar(max),
                @myXml xml;

        -- Parse comma delimited string into a table
        SELECT @myXml = CONVERT(xml,'<root><s>' + REPLACE(@p_SearchColumns,',','</s><s>') + '</s></root>');
        DECLARE Cursor1 CURSOR FOR
                SELECT [Value] = T.c.value('.','varchar(max)') FROM @myXml.nodes('/root/s') T(c);

        -- Create temporary table to hold results
        CREATE TABLE #ListDistinctValuesAndCounts
                (myValue varchar(max), myColumn varchar(max), myCount int);

        -- Iterate through cursor1
        OPEN Cursor1
        FETCH NEXT FROM Cursor1 INTO @ColToProcess
        WHILE @@FETCH_STATUS = 0
        BEGIN
                SET @SqlToExecute = '
                        SELECT DISTINCT
                                ' + LTRIM(@ColToProcess) + ' COLLATE ' + @p_UseCollation + ',
                                ''' + LTRIM(@ColToProcess) + ''',
                                COUNT(*) AS Count
                        FROM
                                ' + @p_SearchTable + '
                        GROUP BY
                                ' + LTRIM(@ColToProcess) + ' COLLATE ' + @p_UseCollation;
                INSERT #ListDistinctValuesAndCounts
                        EXEC(@SqlToExecute);
                FETCH NEXT FROM Cursor1 INTO @ColToProcess
        END
        CLOSE Cursor1;
        DEALLOCATE Cursor1;

        -- Generate final query and execute it
        SET @SqlToExecute = '
                SELECT DISTINCT
                        myValue COLLATE ' + @p_UseCollation + ' AS myValue,
                        myColumn,
                        SUM(myCount) AS myCount
                FROM
                        #ListDistinctValuesAndCounts
                GROUP BY
                        myValue COLLATE ' + @p_UseCollation + ',
                        myColumn
                ORDER BY
                        myColumn,
                                                myValue
                        ';
        EXEC(@SqlToExecute);

END