Calculate hashbytes for table

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