How do I list tables in the order of modification?

General
#1

I have a database with many tables and I want to understand which of my actions is modifying what tables. How do I use cockroach sql client to list tables in order of modifications? Internet answers use sys.tables as the FROM target which does not work:
select * from sys.tables order by modify_date desc;

pq: relation “sys.tables” does not exist

#2

Hey @Jitendra,

Could you provide some more clarification around what you mean when you say

I want to understand which of my actions is modifying what tables.

Are you wanting to see what tables recently had inserts added, or columns added, or values changed?

As for the error you are seeing, that is to be expected, there is no database named sys it might have been referring to the system.

Thanks,

Ron

#3

Thanks for the replay, Ron. I want to list tables that have recently added or updated records. How do I get those?
Jitendra

#4

Hi @Jitendra,

No problem! Thank you for clarifying, however that is not a feature we have on Cockroach. Is there any particular reason that you need this data?

Thanks,

Ron

#5

Why do I need this functionality:
I inherited code that uses roachdb in an orchestration framework. It will speed up understanding if I were to observe which of the many 10s of tables are modified and how as a result of various events. Will also be useful later in debugging. Other SQL dbs seem to support this functionality.

Thanks!

Jitendra

#6

Hi @Jitendra,

Understood. I would suggest using our SQL execution logs, you can read more about it here.

This would allow you to write all of your queries to the clusters log file and can be turned on and off as needed.

Let me know if this helps!

Thanks,

Ron

#7

Thanks Ron, I shall do so!