# How to Utilise the Information Schema in Data Virtuality

**URL:** <https://community.cdata.com/t/how-to-utilise-the-information-schema-in-data-virtuality/1021>\
**Category:** CData Virtuality \[Archived\]\
**Tags:** automation, data-governance, info-schema, data-schema, information-schema, discussion, how-to, metadata, data-virtuality, cdata-virtuality\
**Created:** [February 13, 2025, 12:10pm UTC](https://community.cdata.com/t/how-to-utilise-the-information-schema-in-data-virtuality/1021 "2025-02-13T12:10:10Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![Marijan](https://sea3.discourse-cdn.com/cdata/user_avatar/community.cdata.com/marijan/32/134_2.png) [@Marijan](https://community.cdata.com/u/Marijan)\
**Post date:** [February 13, 2025, 12:11pm UTC](https://community.cdata.com/t/how-to-utilise-the-information-schema-in-data-virtuality/1021/1 "2025-02-13T12:11:10Z")

</div>

## Introduction

One of the often overlooked features of CData Virtuality is the fact that it contains a complete and ready to use metadata repository containing all of the tables, views, columns and data types for all of the connected data sources as well as the virtual views created in the semantic model. These are all available as a set of ANSI-standard read-only views that are updated with every change in the underlying sources and always return the latest state. It provides a structured way to retrieve important information about your data sources without needing to manually inspect each schema.

The **columns** view contains information about all table and view columns in the data sources configured in the CData Virtuality Server. The **tables** view contains information about all of the tables, and **views** contains information about virtual views created in CData Virtuality server.

## Why Use the INFORMATION\_SCHEMA?

Using the **INFORMATION\_SCHEMA** enables users to:

- Retrieve metadata dynamically instead of relying on manual documentation.
- Audit and document schema structures.
- Debug and analyze data models.
- Optimize performance by reviewing indexes and constraints.

### **Common Use Cases**

- Listing all available tables and views.
- Checking column data types and constraints.
- Understanding relationships between tables.
- Reviewing indexes and stored procedures.
- Automation: e.g. create a replication job for all tables in some schema

## Querying the Information Schema

Here are just a few examples of simple but useful use cases for retrieving information from the Information Schema.

**Get all columns of a specific table**

`SELECT * FROM INFORMATION_SCHEMA.columns`

`WHERE table_name = 'your_table_name';`

 ![255eea0c-712b-4ef8-b041-95e4e3910900.jpg](https://us1.discourse-cdn.com/cdata/original/1X/e9b7dcb741d8251ec362bc526c0cc44be54707b5.jpeg)

### &nbsp;

**Find all tables in a specific schema**

`SELECT * FROM INFORMATION_SCHEMA.tables`

`WHERE table_schema = 'your_schema_name';`

 ![91e1ed8e-850a-4cfe-a4d9-65f899d1e71e.jpg](https://us1.discourse-cdn.com/cdata/original/1X/389648a62229ea647c3423283cf8121876dfa560.jpeg)

**Identify Tables Without Columns**

`SELECT table_name FROM INFORMATION_SCHEMA.tables`

`WHERE table_name NOT IN (`

`     SELECT DISTINCT table_name`

`     FROM INFORMATION_SCHEMA.columns`

` );;`

 ![20fd20d9-faa4-4aae-bfa6-d97aa35831d6.png](https://us1.discourse-cdn.com/cdata/original/2X/a/a0ac7b2620a26a04bd80e21e6e9c8050a86b008e.png)

**Retrieve All Column Data Types for a Specific Table**

`SELECT column_name, udt_name AS data_type`

`FROM INFORMATION_SCHEMA.columns`

`WHERE table_schema = 'your_schema_name'  `

`  AND table_name = 'your_table_name';;`

 ![864acca0-cd18-4f61-afb8-9d44e1abda8e.jpg](https://us1.discourse-cdn.com/cdata/original/2X/2/2d1500e1bb2cdce55771137915a91dd481299021.jpeg)

**Generate Dynamic SQL for Auditing Table Changes**

`SELECT`

` 'SELECT COUNT(*) AS row_count, ''' || table_name || ''' AS table_name FROM ' || table_schema || '.' || table_name || ';'`

`FROM INFORMATION_SCHEMA.tables`

`WHERE table_schema = 'your_table_name';`

 ![0b439da0-f3e2-437d-8446-811a4a789fe2.jpg](https://us1.discourse-cdn.com/cdata/original/1X/217efae0f714cee6a0ddd00820febc4d3fb8b7f0.jpeg)

### &nbsp;

**Identify Columns With No Default Value**

`SELECT table_name, column_name`

`FROM INFORMATION_SCHEMA.columns`

`WHERE column_default IS NULL;;`

 ![ef1764d2-4798-48a6-bcde-85b809dae899.jpg](https://us1.discourse-cdn.com/cdata/original/1X/beca32ae0569481e871a0cf4ded429fb094b00d7.jpeg)

### &nbsp;

**Retrieve all tables along with their column details**

`SELECT ``t.table_name,`` c.column_name, ``c.udt_name`

`FROM`

` INFORMATION_SCHEMA.tables t`` JOIN INFORMATION_SCHEMA.columns c `

` ON t.table_name = c.table_name;;`

 ![1c351e24-61ee-4d23-9a6b-d280cd5ccb82.jpg](https://us1.discourse-cdn.com/cdata/original/1X/6eaf4991a7942bcc106ffb4c58c1464e4189180c.jpeg)

## Conclusion

The INFORMATION\_SCHEMA views provide structured metadata access in Data Virtuality and querying this metadata simplifies debugging, schema management, and performance optimisation. You can integrate metadata queries into your workflows and make use of dynamic queries as well as the advanced programming options using virtual procedures in CData Virtuality to streamline and automate data catalog creation, change detection and management and many other aspects of your data management.

To find out more about Information Schema, please visit our [official documentation page](https://docs.datavirtuality.com/v4/information-schema).