> ## Documentation Index
> Fetch the complete documentation index at: https://docs.kinetica.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Useful Catalog Queries

> Copy-paste examples of how to utilize metadata in virtual catalogs with SQL

The catalog tables allow for some useful information to be extracted from the
database.  See [Virtual Catalogs](/content/catalogs) for a complete listing.

<a id="snippet-sql-vc-table-resource" />

## Table Resource Usage

### Table RAM Usage

```sql Kinetica Catalog theme={null}
SELECT
	IF(GROUPING(source_rank) = 1,'Total',STRING(source_rank)) AS "Rank",
	SUM(size) AS "Bytes"
FROM ki_catalog.ki_tiered_objects
WHERE tier = 'RAM'
AND outer_object = '[<schema name>.]<table name>'
GROUP BY ROLLUP(source_rank)
ORDER BY INT(source_rank) NULLS LAST
```

### Table Disk Usage

```sql Kinetica Catalog theme={null}
SELECT
	IF(GROUPING(source_rank) = 1,'Total',STRING(source_rank)) AS "Rank",
	SUM(size) AS "Bytes"
FROM ki_catalog.ki_tiered_objects
WHERE tier = 'PERSIST'
AND outer_object = '[<schema name>.]<table name>'
AND id NOT LIKE 'Wal%'
GROUP BY ROLLUP(source_rank)
ORDER BY INT(source_rank) NULLS LAST
```

<a id="snippet-sql-vc-mv-dep" />

## Materialized View Dependencies

### Specified Materialized View Dependencies

<CodeGroup>
  ```sql ANSI (INFORMATION_SCHEMA) Catalog theme={null}
  SELECT
      dest_table_schema||'.'||dest_table_name AS child_name,
      source_table_schema||'.'||source_table_name AS parent_name,
      source_table_kind AS parent_table_kind
  FROM INFORMATION_SCHEMA.MV_DEPENDENCIES
  WHERE
  	mv_schema = '<mv_schema_name>' AND
  	mv_name = '<mv_name>'
  ORDER BY 1, 2
  ```

  ```sql Kinetica Catalog theme={null}
  SELECT
      child.schema_name||'.'||child.object_name AS child_name,
      d.dep_obj_kind AS child_object_kind,
      parent.schema_name||'.'||parent.object_name AS parent_name,
      d.src_obj_kind AS parent_object_kind
  FROM ki_catalog.ki_depend d
  JOIN ki_catalog.ki_objects root ON
  	root.oid = mv_oid AND
  	schema_name = '<mv_schema_name>' AND
  	object_name = '<mv_name>'
  JOIN ki_catalog.ki_objects parent ON parent.oid = src_obj_oid
  JOIN ki_catalog.ki_objects child ON child.oid = dep_obj_oid
  ORDER BY 1, 3
  ```
</CodeGroup>

### All Materialized View Dependencies

<CodeGroup>
  ```sql ANSI (INFORMATION_SCHEMA) Catalog theme={null}
  SELECT
      mv_schema||'.'||mv_name AS mv_name,
      dest_table_schema||'.'||dest_table_name AS child_name,
      source_table_schema||'.'||source_table_name AS parent_name,
      source_table_kind AS parent_table_kind
  FROM INFORMATION_SCHEMA.MV_DEPENDENCIES
  ORDER BY 1, 2, 3
  ```

  ```sql Kinetica Catalog theme={null}
  SELECT
      root.schema_name || '.' || root.object_name AS mv_name,
      child.schema_name||'.'||child.object_name AS child_name,
      d.dep_obj_kind AS child_object_kind,
      parent.schema_name||'.'||parent.object_name AS parent_name,
      d.src_obj_kind AS parent_object_kind
  FROM ki_catalog.ki_depend d
  JOIN ki_catalog.ki_objects root ON root.oid = mv_oid
  JOIN ki_catalog.ki_objects parent ON parent.oid = src_obj_oid
  JOIN ki_catalog.ki_objects child ON child.oid = dep_obj_oid
  ORDER BY 1, 2, 4
  ```
</CodeGroup>

<a id="snippet-sql-vc-sql-queries" />

## 10 Most Recent SQL Queries

<CodeGroup>
  ```sql ANSI (INFORMATION_SCHEMA) Catalog theme={null}
  SELECT user_name, query_text, start_time
  FROM INFORMATION_SCHEMA.KI_QUERY_HISTORY
  WHERE query_text <> ''
  ORDER BY start_time DESC
  LIMIT 10
  ```

  ```sql Kinetica Catalog theme={null}
  SELECT user_name, query_text, start_time
  FROM ki_catalog.ki_query_history
  WHERE query_text <> ''
  ORDER BY start_time DESC
  LIMIT 10
  ```
</CodeGroup>

<a id="snippet-sql-vc-import-errors" />

## Import/Export Jobs with Errors in the Past Hour

<CodeGroup>
  ```sql ANSI (INFORMATION_SCHEMA) Catalog theme={null}
  SELECT *
  FROM INFORMATION_SCHEMA.KI_LOAD_HISTORY
  WHERE num_error > 0 AND start_time >= TIMESTAMPADD(HOUR, -1, NOW())
  ```

  ```sql Kinetica Catalog theme={null}
  SELECT *
  FROM ki_catalog.ki_load_history
  WHERE num_errors > 0 AND start_time >= TIMESTAMPADD(HOUR, -1, NOW())
  ```
</CodeGroup>

<a id="snippet-sql-vc-security-restrictions" />

## Users with Row/Column Security Restrictions

```sql Kinetica Catalog theme={null}
SELECT
	role_name,
	object_schema_name || '.' || object_name AS object_name,
	cls AS column_level_security,
	rls AS row_level_security
FROM ki_catalog.ki_object_permissions
WHERE permission_type = 'table_read' AND (rls <> '' OR cls <> '')
```

<a id="snippet-sql-vc-roles-size" />

## Role Assignments

```sql Kinetica Catalog theme={null}
SELECT role_name AS assigned_role_name, member_name AS assignee_name
FROM ki_catalog.ki_role_members
ORDER BY 1, 2 DESC
```