I was glad to see on mssqltips a script to generate column list for table in order to by use in hashbytes function later for table comparison : article with original get_hash_field function

But as often I needed to do more than just concatenate all fields. My need is :

• insert a symbol between field to avoid (or limit) the "birthday paradox" : avoid to obtain the same final string if you concat "123" and "45" or you concat "12" and "345"
• replace null field with a dummy string 'µ' for me
• be able to exclude 2 lists of column for build the list of fields : I wanted all columns of the table except 1 or more fields nor fields in the key that was not needed for me. In the orginal function this list was static

For that I use store procedure instead of function and dynamic sql was mandatory (for the parameters @p_exception_column_list and @p_key_column_list that are strings with quotes) :

CREATE  procedure [dbo].[sp_get_hash_fields2] (@p_table_name VARCHAR(100),@p_schema_name VARCHAR(20), 

@p_exception_column_list nvarchar(2000),

@p_key_column_list nvarchar(2000),

@sqlStringOut nvarchar(max) output)


AS

BEGIN

    --DECLARE @sqlString as nvarchar(max)

DECLARE @sqlStringtmp as nvarchar(max)

declare @sqlStatement as nvarchar(max)

    --SET @sqlString = ''


set @sqlStatement= N'

--DECLARE @sqlString varchar(max)

SET @sqlString='''';

    SELECT @sqlString = @sqlString +

      CASE DATA_TYPE 

      WHEN ''int'' THEN ''ISNULL(RTRIM(CONVERT(varchar(10),'' + COLUMN_NAME + '')),''''µ'''')''

 WHEN ''bigint'' THEN ''ISNULL(RTRIM(CONVERT(varchar(20),'' + COLUMN_NAME + '')),''''µ'''')''

 WHEN ''smallint'' THEN ''ISNULL(RTRIM(CONVERT(varchar(10),'' + COLUMN_NAME + '')),''''µ'''')''

 WHEN ''tinyint'' THEN ''ISNULL(RTRIM(CONVERT(varchar(10),'' + COLUMN_NAME + '')),''''µ'''')''

      WHEN ''datetime'' THEN ''ISNULL(RTRIM(CONVERT(varchar(20),'' + COLUMN_NAME + '',120)),''''µ'''')''  

      WHEN ''datetime2'' THEN ''ISNULL(RTRIM(CONVERT(varchar(20),'' + COLUMN_NAME + '',120)),''''µ'''')''

      WHEN ''date'' THEN ''ISNULL(RTRIM(CONVERT(varchar(20),'' + COLUMN_NAME + '',120)),''''µ'''')'' 

      WHEN ''bit'' THEN ''ISNULL(RTRIM(CONVERT(varchar(1),'' + COLUMN_NAME + '')),''''µ'''')''  

      WHEN ''decimal'' THEN ''ISNULL(RTRIM(CONVERT(varchar(''+ CONVERT(varchar(2),NUMERIC_PRECISION) +''),'' + COLUMN_NAME + '')),''''µ'''')'' 

      ELSE ''ISNULL(RTRIM('' + COLUMN_NAME + ''),''''µ'''')''

      END + ''+''+''''''§''''''+''+''

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_SCHEMA = '''+@p_schema_name+''' and TABLE_NAME = '''+@p_table_name+'''

    AND COLUMN_NAME NOT IN ('+@p_exception_column_list+')

AND COLUMN_NAME NOT IN ('+@p_key_column_list+')';


--print @sqlStatement;

set @sqlStatement=cast(@sqlStatement as nvarchar(max));


execute sp_executesql @sqlStatement , N'@sqlString varchar(max) OUTPUT', @sqlString=@sqlStringtmp output;

--print @sqlStringtmp


set @sqlStringOut =LEFT(ISNULL(@sqlStringtmp, ''),LEN(@sqlStringtmp)-1)

                                

    --RETURN LEFT(ISNULL(@sqlString, ''),LEN(@sqlString)-1)

END 

One usage could be like that :

USE [TPCH]

GO


DECLARE @RC int

DECLARE @p_table_name varchar(100)

DECLARE @p_schema_name varchar(20)

DECLARE @p_exception_column_list nvarchar(2000)

DECLARE @p_key_column_list nvarchar(2000)

DECLARE @p_ColList4Hash nvarchar(max)

DECLARE @sqlQuery nvarchar(max)


SET @p_table_name='H_Lineitem'

SET @p_schema_name='dbo'

SET @p_exception_column_list ='''l_partkey'''

SET @p_key_column_list='''l_orderkey'',''l_linenumber'''



-- TODO: Set parameter values here.


EXECUTE @RC = [dbo].[sp_get_hash_fields2] 

   @p_table_name

  ,@p_schema_name

  ,@p_exception_column_list

  ,@p_key_column_list

  ,@sqlStringOut=@p_ColList4Hash OUTPUT

;





set @sqlQuery=

'

--DECLARE @pagenumber int = 1; --Page to return

--DECLARE @pagesize int = 10;  --number of lines to return per page

DECLARE @total int;

WITH    CTE1 --Do your primary Query and filtering in the inner Common Table Expression

          AS ( SELECT

                  '+ replace(@p_key_column_list,'''','') + ','

 --+@p_ColList4Hash+' as ColList'+','+

 +'HASHBYTES(''MD5'','+@p_ColList4Hash+') as HashBytesOnColList'+

                ',   ROW_NUMBER() OVER ( ORDER BY '+ replace(@p_key_column_list,'''','') + ' ASC ) AS line --This order by Determines your sort order

                ,   _LineCount = COUNT(*) OVER ( )

                FROM

                    '+@p_schema_name+'.'+@p_table_name+'

WHERE

[l_suppkey]=7162

             )

     SELECT TOP ( @pagesize ) -- Diplay results in outer Query

'+ replace(@p_key_column_list,'''','') + ',

--ColList,

HashBytesOnColList'+

        ',   _LineCount

        ,   ( _LineCount / @pagesize ) _PgCount

        FROM

            CTE1

        WHERE

            line > ( @pagenumber - 1 ) * @pagesize

        ORDER BY line --Must have an order by statement to make TOP Deterministic'


--PRINT @sqlQuery


EXECUTE sp_executesql @sqlQuery, N'@pagenumber int, @pagesize int', @pagenumber=1,@pagesize=100 

The query @sqlQuery generated (Nota that Order_Key, line_number, and part_key are not in the concatenation of column passed to hashbytes) :

DECLARE @pagenumber int = 1; --Page to return
DECLARE @pagesize int = 10;  --number of lines to return per page
DECLARE @total int;

WITH    CTE1 --Do your primary Query and filtering in the inner Common Table Expression

          AS ( SELECT

                  l_orderkey,l_linenumber,HASHBYTES('MD5',ISNULL(RTRIM(CONVERT(varchar(10),l_suppkey)),'µ')+'§'+ISNULL(RTRIM(l_quantity),'µ')+'§'+ISNULL(RTRIM(l_extendedprice),'µ')+'§'+ISNULL(RTRIM(l_discount),'µ')+'§'+ISNULL(RTRIM(l_tax),'µ')+'§'+ISNULL(RTRIM(l_returnflag),'µ')+'§'+ISNULL(RTRIM(l_linestatus),'µ')+'§'+ISNULL(RTRIM(CONVERT(varchar(20),l_shipdate,120)),'µ')+'§'+ISNULL(RTRIM(CONVERT(varchar(20),l_commitdate,120)),'µ')+'§'+ISNULL(RTRIM(CONVERT(varchar(20),l_receiptdate,120)),'µ')+'§'+ISNULL(RTRIM(l_shipinstruct),'µ')+'§'+ISNULL(RTRIM(l_shipmode),'µ')+'§'+ISNULL(RTRIM(l_comment),'µ')+'§') as HashBytesOnColList,   ROW_NUMBER() OVER ( ORDER BY l_orderkey,l_linenumber ASC ) AS line --This order by Determines your sort order

                ,   _LineCount = COUNT(*) OVER ( )

                FROM

                    dbo.H_Lineitem

WHERE

[l_suppkey]=7162 -- example

             )

     SELECT TOP ( @pagesize ) -- Diplay results in outer Query

l_orderkey,l_linenumber,

--ColList,

HashBytesOnColList,   _LineCount

        ,   ( _LineCount / @pagesize ) _PgCount

        FROM

            CTE1

        WHERE

            line > ( @pagenumber - 1 ) * @pagesize

        ORDER BY line --Must have an order by statement to make TOP Deterministic 

and return that

Hope that help !