Overview
Are you working in your model IDE and notice there are entire schemas and/or tables missing from the list? It’s possible the OMNI_USER created on your database doesn’t have the appropriate permissions to view or query those tables so they are not coming through into Omni.
This article shares some helpful information schema queries to help you troubleshoot if the OMNI_USER can access the schemas or tables (views) that are not showing up in the Omni IDE.
Steps to utilize these queries:
-
From your Omni homepage, select
+ New Analysisin the top right. -
Select to query the model that is not showing the desired schemas or views.
-
In the top right of the workbook, select the SQL editor.
-
Typically, if a schema or table isn’t showing up in the list it’s because the
OMNI_USERdefined on your database does not have the appropriate permissions to view or query that table. -
We recommend running a simple table query (Copy and paste the desired example query based on the SQL dialect you are using) on your database’s information schema to see a list of schemas or tables that the
OMNI_USERhas access to. -
If the table you’re looking for is not listed, then the Omni user’s permissions to view or query that schema or table need to be adjusted.
I’ve added additional queries that may be of interest but typically querying the table should give you a good sense of what the OMNI_USER has access to.
Information Schema Example Queries by Dialect
Snowflake
This query retrieves the schema, table name, and type of tables in a specific schema:
Query for Tables:
SELECT table_schema,
table_name,
table_type
FROM information_schema.tables
WHERE table_schema = 'your_schema_name'
ORDER BY table_name;
Query for Columns:
To get column-level details (like column name, data type, etc.) for all tables in a schema:
SELECT table_schema,
table_name,
column_name,
data_type,
ordinal_position
FROM information_schema.columns
WHERE table_schema = 'your_schema_name'
ORDER BY table_name, ordinal_position;
Query for Views:
If you want to query all views in a particular schema:
SELECT table_schema,
table_name,
view_definition
FROM information_schema.views
WHERE table_schema = 'your_schema_name'
ORDER BY table_name;
BigQuery
In BigQuery, you must include the fully qualified names (i.e., project_id.dataset_name) when querying INFORMATION_SCHEMA.
Query for Tables:
To list the tables in a specific dataset (BigQuery’s equivalent to a schema). Replace project_id and dataset_name with your specific project and dataset names:
SELECT table_schema,
table_name,
table_type
FROM `project_id`.`dataset_name`.INFORMATION_SCHEMA.TABLES
ORDER BY table_name;
Query for Columns:
To retrieve column details from the tables in a dataset:
SELECT table_schema,
table_name,
column_name,
data_type,
ordinal_position
FROM `project_id`.`dataset_name`.INFORMATION_SCHEMA.COLUMNS
ORDER BY table_name, ordinal_position;
Query for Views:
To list all views in a specific dataset:
SELECT table_schema,
table_name,
is_insertable_into
FROM `project_id`.`dataset_name`.INFORMATION_SCHEMA.VIEWS
ORDER BY table_name;
MotherDuck
MotherDuck uses a SQLite-compatible query engine, and while it doesn’t have an INFORMATION_SCHEMA exactly like in Redshift, Snowflake, or BigQuery, it offers similar metadata through SQLite’s system catalogs. You can retrieve metadata about tables and columns using sqlite_master and PRAGMA commands. In these queries, replace 'table_name' with the name of your table. This allows you to inspect various metadata aspects of your database in MotherDuck, much like you would with INFORMATION_SCHEMA in other databases.
Query for Tables:
SELECT name AS table_name,
type
FROM sqlite_master
WHERE type = 'table'
ORDER BY name;
Query for Columns:
To get column details for a specific table, you can use the PRAGMA table_info() function. This will return information like cid, name (column name), type (data type), and more for the specified table:
PRAGMA table_info('table_name');
Query for Views:
To list all views in the database:
SELECT name AS view_name
FROM sqlite_master
WHERE type = 'view'
ORDER BY name;
Query for Indexes:
To list all indexes for a specific table:
PRAGMA index_list('table_name');
Clickhouse
In ClickHouse, replace 'your_database_name' and 'your_table_name' with the actual database and table names you’re working with. These queries allow you to explore metadata in ClickHouse much like you would with INFORMATION_SCHEMA in other databases.
Query for Tables:
To retrieve a list of all tables in a specific database:
SELECT database,
name AS table_name,
engine
FROM system.tables
WHERE database = 'your_database_name'
ORDER BY table_name;
Query for Columns:
To retrieve details about the columns of tables in a specific database:
SELECT database,
table AS table_name,
name AS column_name,
type AS data_type,
position AS ordinal_position
FROM system.columns
WHERE database = 'your_database_name'
ORDER BY table_name, ordinal_position;
Query for Views:
ClickHouse doesn’t have a specific table type for views in the same way as other databases, but you can query system tables to find “materialized views” or “live views”:
SELECT database,
name AS view_name,
engine
FROM system.tables
WHERE database = 'your_database_name'
AND engine IN ('MaterializedView', 'LiveView')
ORDER BY view_name;
Query for Indexes:
ClickHouse supports “skipping indexes” (which are not typical indexes like in other databases). To list these skipping indexes for a specific table:
SELECT *
FROM system.data_skipping_indices
WHERE database = 'your_database_name'
AND table = 'your_table_name';
Query for Table Size and Row Count:
To retrieve the size and row count for each table in a specific database, you can use the system.parts table:
SELECT table AS table_name,
sum(rows) AS row_count,
sum(bytes_on_disk) AS size_in_bytes
FROM system.parts
WHERE database = 'your_database_name'
AND active
GROUP BY table
ORDER BY table_name;
Query for Partitions:
To check partitions of a table:
SELECT partition,
name AS partition_id,
sum(rows) AS row_count,
sum(bytes_on_disk) AS size_in_bytes
FROM system.parts
WHERE database = 'your_database_name'
AND table = 'your_table_name'
AND active
GROUP BY partition, name
ORDER BY partition;
Postgres
In PostgreSQL, replace 'your_schema_name' and 'your_table_name' with the appropriate schema and table names. These queries will help you explore metadata in PostgreSQL.
Query for Tables:
To retrieve information about all tables in a specific schema:
SELECT table_catalog,
table_schema,
table_name,
table_type
FROM information_schema.tables
WHERE table_schema = 'your_schema_name'
ORDER BY table_name;
Query for Columns:
To retrieve details about columns in a specific schema:
SELECT table_catalog,
table_schema,
table_name,
column_name,
data_type,
ordinal_position
FROM information_schema.columns
WHERE table_schema = 'your_schema_name'
ORDER BY table_name, ordinal_position;
Query for Views:
To list all views in a specific schema:
SELECT table_catalog,
table_schema,
table_name,
view_definition
FROM information_schema.views
WHERE table_schema = 'your_schema_name'
ORDER BY table_name;
Query for Indexes:
PostgreSQL provides an pg_indexes view, which you can use to retrieve index information for a specific schema or table:
SELECT schemaname AS schema_name,
tablename AS table_name,
indexname AS index_name,
indexdef AS index_definition
FROM pg_indexes
WHERE schemaname = 'your_schema_name'
AND tablename = 'your_table_name'
ORDER BY indexname;
Query for Table Size and Row Count:
PostgreSQL has built-in functions to retrieve table size and row count. You can use the following to get both:
SELECT table_name,
pg_total_relation_size(quote_ident(table_name)) AS size_in_bytes,
(xpath('/row/cnt/text()', query_to_xml('SELECT COUNT(*) AS cnt FROM ' || quote_ident(table_name), true, true, '')))[1]::text::int AS row_count
FROM information_schema.tables
WHERE table_schema = 'your_schema_name'
AND table_type = 'BASE TABLE'
ORDER BY table_name;
Alternatively, for a simpler row count and size, you can use:
SELECT relname AS table_name,
n_live_tup AS row_count,
pg_total_relation_size(relid) AS size_in_bytes
FROM pg_stat_user_tables
ORDER BY relname;
Redshift
To query the information_schema in a Redshift database, you can use the following SQL syntax. This example retrieves basic details about the tables within a specific schema:
SELECT table_schema,
table_name,
table_type
FROM information_schema.tables
WHERE table_schema = 'your_schema_name'
ORDER BY table_name;
If you want to get more specific information, such as column details, you can modify the query accordingly. For example, to get details about columns:
SELECT table_schema,
table_name,
column_name,
data_type,
ordinal_position
FROM information_schema.columns
WHERE table_schema = 'your_schema_name'
ORDER BY table_name, ordinal_position;
MySQL
In MySQL, replace 'your_database_name' with the actual name of your schema (database), and 'your_table_name' where necessary.
Query for Tables:
To retrieve information about all tables in a specific schema (database):
SELECT table_schema,
table_name,
table_type
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY table_name;
Query for Columns:
To retrieve details about the columns of tables in a specific schema:
SELECT table_schema,
table_name,
column_name,
data_type,
ordinal_position
FROM information_schema.columns
WHERE table_schema = 'your_database_name'
ORDER BY table_name, ordinal_position;
Query for Views:
To list all views in a specific schema:
SELECT table_schema,
table_name,
view_definition
FROM information_schema.views
WHERE table_schema = 'your_database_name'
ORDER BY table_name;
Query for Indexes:
To list all indexes in a specific table:
SELECT table_schema,
table_name,
index_name,
column_name,
non_unique
FROM information_schema.statistics
WHERE table_schema = 'your_database_name'
AND table_name = 'your_table_name'
ORDER BY index_name, seq_in_index;
Query for Table Size and Row Count:
To get the size (in bytes) and row count for each table in a schema:
SELECT table_name,
table_rows,
data_length + index_length AS size_in_bytes
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY table_name;
Databricks
In Databricks SQL, replace 'your_schema_name', 'your_database_name', and 'your_table_name' with the appropriate names for your database objects.
Query for Tables:
To retrieve a list of all tables within a specific schema:
SELECT table_catalog,
table_schema,
table_name,
table_type
FROM system.information_schema.tables
WHERE table_schema = 'your_schema_name'
ORDER BY table_name;
Query for Columns:
To retrieve column details of tables in a specific schema:
SELECT table_catalog,
table_schema,
table_name,
column_name,
data_type,
ordinal_position
FROM system.information_schema.columns
WHERE table_schema = 'your_schema_name'
ORDER BY table_name, ordinal_position;
Query for Views:
To list all views in a specific schema:
SELECT table_catalog,
table_schema,
table_name,
view_definition
FROM system.information_schema.views
WHERE table_schema = 'your_schema_name'
ORDER BY table_name;
Microsoft SQL Server
In SQL Server, replace 'your_schema_name' and 'your_table_name' with the actual names you’re working with. These queries will help you explore metadata in a SQL Server environment.
Query for Tables:
To retrieve a list of all tables within a specific schema:
SELECT table_catalog,
table_schema,
table_name,
table_type
FROM information_schema.tables
WHERE table_schema = 'your_schema_name'
ORDER BY table_name;
Query for Columns:
To get details about the columns in a specific schema:
SELECT table_catalog,
table_schema,
table_name,
column_name,
data_type,
ordinal_position
FROM information_schema.columns
WHERE table_schema = 'your_schema_name'
ORDER BY table_name, ordinal_position;
Query for Views:
To list all views in a specific schema:
SELECT table_catalog,
table_schema,
table_name,
view_definition
FROM information_schema.views
WHERE table_schema = 'your_schema_name'
ORDER BY table_name;
Query for Indexes:
To retrieve all indexes for a specific table, you can query the system catalog sys.indexes:
SELECT i.name AS index_name,
i.index_id,
i.type_desc AS index_type,
c.name AS column_name,
ic.index_column_id
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE OBJECT_NAME(i.object_id) = 'your_table_name'
ORDER BY i.name, ic.index_column_id;
Query for Table Size and Row Count:
To get row count and size for each table in a database, you can use the sys.dm_db_partition_stats and sys.objects views:
SELECT t.name AS table_name,
SUM(p.rows) AS row_count,
SUM(a.total_pages) * 8 AS total_size_kb
FROM sys.tables t
JOIN sys.partitions p ON t.object_id = p.object_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY t.name
ORDER BY t.name;