Calculate hashbytes for table

[Former Member]

Sometimes it is necessary to make sure that content of two tables are the same or to monitor changes to the table.

For example for MS SQL it is possible to do that with CHECKSUM_AGG function

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM table

As far as I know there is no similar aggregation function for Sybase ASE, but some close functionality can be achieved with XOR + update trick

create procedure sp__hashcode

 @objectname varchar(255)

,@excludelist varchar(16384) = null

,@includelist varchar(16384) = null

,@whereclause varchar(16384) = null

,@hashalgorithm varchar(20) = 'MD5'

,@using varchar(255) = null

,@hash bigint = 0 output

,@debug bit = 1

as

begin

    declare @objectid int

           ,@columnlist varchar(16384)

           ,@rowcount int

    select @objectid = object_id(@objectname), @hash = isnull(@hash,0)

    if @objectid is null begin

        /* 17461, "Object does not exist in this database." */

        raiserror 17461

        return (1)

    end

    /* Create temporary table for column list */

    create table #columnlist (

        name varchar(255)

    )

    insert into #columnlist

    select distinct c.name

    from syscolumns c

    join systypes t on (c.type = t.type)

    where c.id = @objectid

    and t.name not in ('image','text','unitext') -- ignore LOBs

    and (isnull(c.xstatus,0) & 1) != 1 -- ignore off row

    /* Delete columns which exists in exclude list */

    if @excludelist is not null begin

        set @excludelist = str_replace(@excludelist, '''', '''''')

        set @excludelist = '''' + str_replace(@excludelist, ',', ''',''') + ''''

        if @debug = 1

            print 'Exclude list: %1!', @excludelist

        execute ('delete from #columnlist where name in (' + @excludelist + ')')

    end

    /* Delete columns which not exists in include list */

    if @includelist is not null begin

        set @includelist = str_replace(@includelist, '''', '''''')

        set @includelist = '''' + str_replace(@includelist, ',', ''',''') + ''''

        if @debug = 1

            print 'Include list: %1!', @includelist

        execute ('delete from #columnlist where name not in (' + @includelist + ')')

    end

    /* Put columns into the comma separated list */

    update #columnlist

    set @columnlist = @columnlist + case when @columnlist is null then null else ',' end + name

    if @debug = 1

        print 'Column list: %1!', @columnlist

    if @columnlist is not null

        if @using is null

            execute ( 'update ' + @objectname + ' set @hash=@hash^convert(bigint,hashbytes(''' + @hashalgorithm + ''',' + @columnlist + ')) ' + @whereclause )

        else

            execute ( 'update ' + @objectname + ' set @hash=@hash^convert(bigint,hashbytes(''' + @hashalgorithm + ''',' + @columnlist + ',using ' + @using + ')) ' + @whereclause )

    select @rowcount = @@rowcount

    if @debug = 1 begin

        print 'Rows processed: %1!', @rowcount

        print 'Hash: %1!', @hash

    end

    return (0)

end

go

Example of usage:

sp__hashcode sysobjects, @includelist='name,id,uid,type', @whereclause='where id < 20' 

Definitely, there is no one hundred percent guarantee that two different tables will return distinct hashes, at least because hashbytes calculates hash based on the underlying bytes, and in case of different data types with same bytes it returns the same value. This query demonstrates it

select hashbytes ('MD5', cast(49 as tinyint)), hashbytes ('MD5', '1') 

Also it is quite heavy, and have other limitations (like lengths of column names) but still useful for quick comparation of tables and watching for changes for relevantly small tables.

p.s. criticism is welcome