How to get DB Creation Date,User name and other stats in SS Cluster

1

Hi,

I want to get Statistics of Database Creation date, Which user created the DB, Table creation date, Which commands run on particular DB?

Pls let me know how to get above details in SS Cluster by runnng commands in SS Studio GUI.

I have installed SS using Tarball on Linux with SS version: 8.0.9

2

Try

use information_schema;
select * from distributed_databases;
select * from schemata;
select * from tables;

The user who created the table is available in the TABLES table.

3

I think you should add the database creation date to DATABASE INFO in the singlestore studio

4

How i get the create date of a Database?

5

We don’t have that information available directly in information_schema. You can approximate it in some cases based on this:

One of the system views in SingleStore that contains metadata is MV_COLUMNAR_SEGMENT_INDEX which includes a CREATION_TIME column, but this pertains to columnstore segments and not to the database as a whole.

6

In SingleStore (formerly known as MemSQL), you can gather information about database creation date, table creation date, and user actions using various SQL queries and system views. Here’s how you can obtain this information:

  1. Database Creation Date:
    You can find the creation date of a database by querying the information_schema.DATABASES system view. Here’s the SQL query:

    SELECT database_name, create_time
FROM information_schema.DATABASES
WHERE database_name = 'your_database_name';

  2. User who created the Database:
    SingleStore does not directly track the user who created a database. However, if you maintain an audit trail or logging system, you may be able to retrieve this information from your logs.

  3. Table Creation Date:
    Table creation date information can be obtained from the information_schema.TABLES system view. Here’s the SQL query:

    SELECT table_name, create_time
FROM information_schema.TABLES
WHERE database_name = 'your_database_name';

  4. Commands run on a particular Database:
    SingleStore does not store a history of executed commands by default. However, you can set up auditing or logging mechanisms to track commands executed against the database. Additionally, if you’re using the SingleStore Studio GUI, you can view the history of executed queries within the Studio interface.

Please note that while SingleStore provides system views to gather information about database objects, user actions, and more, it may not cover every specific requirement you have. You may need to supplement this information with external logging or auditing mechanisms depending on your use case and requirements.