# Why are my schemas and/or tables not appearing in the model IDE?
**URL:** https://community.omni.co/t/why-are-my-schemas-and-or-tables-not-appearing-in-the-model-ide/156
**Category:** Modeling
**Created:** [September 23, 2024, 3:57pm UTC](https://community.omni.co/t/why-are-my-schemas-and-or-tables-not-appearing-in-the-model-ide/156 "2024-09-23T15:57:55Z")
**Posts on this page:** 2
**Page:** 1
### Author:  [@aklance](https://community.omni.co/u/aklance)
#### Post date: [September 23, 2024, 3:57pm UTC](https://community.omni.co/t/why-are-my-schemas-and-or-tables-not-appearing-in-the-model-ide/156/1 "2024-09-23T15:57:55Z")
# 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:
1. From your Omni homepage, select `+ New Analysis` in the top right.
2. Select to query the model that is not showing the desired schemas or views.
3. In the top right of the workbook, select the SQL editor.
4. Typically, if a schema or table isn’t showing up in the list it’s because the `OMNI_USER` defined on your database does not have the appropriate permissions to view or query that table.
5. 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_USER` has access to.
6. 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:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
PRAGMA table_info('table_name');
```
#### Query for Views:
To list all views in the database:
```auto
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:
```auto
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:
```auto
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:
```auto
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”:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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):
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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:
```auto
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`:
```auto
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:
```auto
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;
```
---
### Author:  [@dr\_phil](https://community.omni.co/u/dr_phil)
#### Post date: [September 30, 2025, 10:04pm UTC](https://community.omni.co/t/why-are-my-schemas-and-or-tables-not-appearing-in-the-model-ide/156/2 "2025-09-30T22:04:56Z")
Another reason might be if the view is ignored in the Omni model.
This can happen if you had the view at some point but deleted it. If you change your browse mode in the model editor from “Combined” to “Model” you may find additional views. Check the view file in question for `ignored: true` and delete that line. Refresh your schema and see if the model populates.
