Stored Procedure to List Distinct Values and Counts

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

1.  IF OBJECT_ID('usp_ListDistinctValuesAndCounts', 'P') IS NOT NULL 
2.      DROP PROCEDURE [usp_ListDistinctValuesAndCounts]; 
3.  GO 
4.   
5.  CREATE PROCEDURE [usp_ListDistinctValuesAndCounts] 
6.  ( 
7.            @p_SearchTable VARCHAR(max), 
8.            @p_SearchColumns VARCHAR(max), 
9.            @p_UseCollation VARCHAR(max) = 'Latin1_General_CS_AS' 
10.  ) 
11.  AS 
12.  /***************************************************************************************************************** 
13.  ** Author:      Joel Lipman                                                                                     ** 
14.  ** Created On:  26 October 2012                                                                                 ** 
15.  ** Revision:    v1.0                                                                                            ** 
16.  ** Description: Stored Procedure to list distinct values and counts (defaults to case and accent-sensitive).    ** 
17.  **                                                                                                              ** 
18.  ** Usage:                                                                                                       ** 
19.  **    EXEC [usp_ListDistinctValuesAndCounts] 'myDB.dbo.myTable', 'myColumn1,myColumn2', 'Latin1_General_CS_AS'; ** 
20.  **                                                                                                              ** 
21.  ** Return Values : Table of three columns:                                                                      ** 
22.  **    - myValue:  the distinct values                                                                           ** 
23.  **    - myColumn: the column this value was found in.                                                           ** 
24.  **    - myCount: the number of times this value was found in this column.                                       ** 
25.  **                                                                                                              ** 
26.  ** Link to other SQL Server collations: http://msdn.microsoft.com/en-us/library/ms180175.aspx                   ** 
27.  **                                                                                                              ** 
28.  ****************************************************************************************************************** 
29.  ** Change History:                                                                                              ** 
30.  ****************************************************************************************************************** 
31.  ** Date:                By:             Rev:        Desc:                                                       ** 
32.  ** ------------------------------------------------------------------------------------------------------------ ** 
33.  ** 26/10/2012           Jlipman         1.0         Created                                                     ** 
34.  **                                                                                                              ** 
35.  *****************************************************************************************************************/ 
36.  BEGIN 
37.   
38.          -- Declare some variables to use 
39.          DECLARE 
40.                  @ColToProcess varchar(max), 
41.                  @SqlToExecute varchar(max), 
42.                  @myXml xml; 
43.   
44.          -- Parse comma delimited string into a table 
45.          SELECT @myXml = CONVERT(xml,'<root><s>' + REPLACE(@p_SearchColumns,',','</s><s>') + '</s></root>'); 
46.          DECLARE Cursor1 CURSOR FOR 
47.                  SELECT [Value] = T.c.value('.','varchar(max)') FROM @myXml.nodes('/root/s') T(c); 
48.   
49.          -- Create temporary table to hold results 
50.          CREATE TABLE #ListDistinctValuesAndCounts 
51.                  (myValue varchar(max), myColumn varchar(max), myCount int); 
52.   
53.          -- Iterate through cursor1 
54.          OPEN Cursor1 
55.          FETCH NEXT FROM Cursor1 INTO @ColToProcess 
56.          WHILE @@FETCH_STATUS = 0 
57.          BEGIN 
58.                  SET @SqlToExecute = ' 
59.                          SELECT DISTINCT 
60.                                  ' + LTRIM(@ColToProcess) + ' COLLATE ' + @p_UseCollation + ', 
61.                                  ''' + LTRIM(@ColToProcess) + ''', 
62.                                  COUNT(*) AS Count 
63.                          FROM 
64.                                  ' + @p_SearchTable + ' 
65.                          GROUP BY 
66.                                  ' + LTRIM(@ColToProcess) + ' COLLATE ' + @p_UseCollation; 
67.                  INSERT #ListDistinctValuesAndCounts 
68.                          EXEC(@SqlToExecute); 
69.                  FETCH NEXT FROM Cursor1 INTO @ColToProcess 
70.          END 
71.          CLOSE Cursor1; 
72.          DEALLOCATE Cursor1; 
73.   
74.          -- Generate final query and execute it 
75.          SET @SqlToExecute = ' 
76.                  SELECT DISTINCT 
77.                          myValue COLLATE ' + @p_UseCollation + ' AS myValue, 
78.                          myColumn, 
79.                          SUM(myCount) AS myCount 
80.                  FROM 
81.                          #ListDistinctValuesAndCounts 
82.                  GROUP BY 
83.                          myValue COLLATE ' + @p_UseCollation + ', 
84.                          myColumn 
85.                  ORDER BY 
86.                          myColumn, 
87.                                                  myValue 
88.                          '; 
89.          EXEC(@SqlToExecute); 
90.   
91.  END