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
- 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