CODEWITHSUNDEEP
sqlsybase
Rudy
Rudy

Reputation: 7044

Check if sybase table updated previously

I am curious if there is a way to know if a sybase table has been updated /added previously? The table is generic, I am not allowed to use trigger or creating something like an updateDate field. Checking row count changes is not an option.

My first idea was to check the sysobjects table, but from what I see using the sql below, I don't think there is column storing any useful information for solving my case.

select name,userstat,uid, type, sysstat, indexdel, schemacnt,sysstat2,sysstat3, crdate,expdate,deltrig,instrig,ckfirst,cache, objspare,versionts,loginame,identburnmax,spacestate,erlchgts,lobcomp_lvl
from sysobjects o where type = 'U'

Any other idea on how to know whether the table has been updated?

Upvotes: 1

Views: 8963

Answers (2)

hui chen
hui chen

Reputation: 1074

If you database admin set up the systabstats update daily, you can use the following query to get the last modified/updated date of a table.

select MAX(statmoddate) as last_modified_date
from systabstats
where id = object_id('table_name')

Upvotes: 1

Rudy
Rudy

Reputation: 7044

Found a way using datachange function.

select convert(varchar(30),o.name) AS table_name,
row_count(db_id(), o.id) AS row_count,
datachange('AAA_TABLE', null, null) AS changes
from sysobjects o
where type = 'U' and o.name='AAA_TABLE'
order by table_name

Before updating the table result was : enter image description here

After updating with below SQL :

update AAA_TABLE set TYPE='1234' where ID=1 //previously TYPE was 1233

Result changed to :

enter image description here

Field "changes" was updated with new value, seems this is an ok solution, but I am still open with any other idea.

Upvotes: 1

Related Questions