VW_DATASET_METADATA_OBJECTS

Overview

VW_DATASET_METADATA_OBJECTS provides a flat, searchable list of every metadata object (schemas, tables, and columns) across all data sources. Unlike VW_DATASET_TABLES and VW_DATASET_COLUMNS which are rich in profile data and custom fields, this view is optimized for lookups, search, and object-level joins via the OBJECT_KEY column.

Use this view for searching across your entire metadata catalog, building navigation aids, and joining tests or executions to their linked objects.

Schema

Relationships

VW_DATASET_METADATA_OBJECTS
    │
    ├── OBJECT_KEY → VW_DATASET_TESTS.TEST_PRIMARY_METADATA_LINK_OBJECT_KEY
    ├── OBJECT_KEY → VW_DATASET_TESTS.CONTROL_PRIMARY_METADATA_LINK_OBJECT_KEY
    ├── OBJECT_KEY → VW_DATASET_TEST_EXECUTIONS.TEST_PRIMARY_METADATA_LINK_OBJECT_KEY
    ├── OBJECT_KEY → VW_DATASET_TABLES.TABLE_KEY (where OBJECT_TYPE = 'Table' or 'View')
    ├── OBJECT_KEY → VW_DATASET_COLUMNS.COLUMN_KEY (where OBJECT_TYPE = 'Column')
    └── DATA_SOURCE_ID → VW_DATASET_TABLES.DATA_SOURCE_ID

Example Queries

Search for Objects by Name Pattern

SELECT
    OBJECT_TYPE,
    DATA_SOURCE_NAME,
    METADATA_PATH,
    OBJECT_URL
FROM REPOSITORY.VW_DATASET_METADATA_OBJECTS
WHERE OBJECT_NAME ILIKE '%customer%'
ORDER BY OBJECT_TYPE, METADATA_PATH;

Count Objects per Data Source

SELECT
    DATA_SOURCE_NAME,
    OBJECT_TYPE,
    COUNT(*) AS OBJECT_COUNT
FROM REPOSITORY.VW_DATASET_METADATA_OBJECTS
GROUP BY DATA_SOURCE_NAME, OBJECT_TYPE
ORDER BY DATA_SOURCE_NAME, OBJECT_TYPE;

Find All Tests Linked to a Metadata Object

SELECT
    mo.METADATA_PATH,
    mo.OBJECT_TYPE,
    t.TEST_NAME,
    t.TEST_TYPE,
    t.OVERALL_RESULT_STATUS
FROM REPOSITORY.VW_DATASET_METADATA_OBJECTS mo
JOIN REPOSITORY.VW_DATASET_TESTS t
    ON t.TEST_PRIMARY_METADATA_LINK_OBJECT_KEY = mo.OBJECT_KEY
WHERE mo.METADATA_PATH = 'PUBLIC.DIM_CUSTOMER'
    AND t.IS_ACTIVE_VERSION = TRUE;

Metadata Object Hierarchy

-- Show all tables and their column counts per schema
SELECT
    DATA_SOURCE_NAME,
    PARENT_OBJECT_NAME AS SCHEMA_NAME,
    OBJECT_NAME AS TABLE_NAME,
    (SELECT COUNT(*)
     FROM REPOSITORY.VW_DATASET_METADATA_OBJECTS child
     WHERE child.OBJECT_TYPE = 'Column'
       AND child.PARENT_OBJECT_NAME = mo.OBJECT_NAME
       AND child.DATA_SOURCE_ID = mo.DATA_SOURCE_ID) AS COLUMN_COUNT
FROM REPOSITORY.VW_DATASET_METADATA_OBJECTS mo
WHERE mo.OBJECT_TYPE IN ('Table', 'View')
ORDER BY DATA_SOURCE_NAME, PARENT_OBJECT_NAME, OBJECT_NAME;

VW_DATASET_METADATA_OBJECT_PROPERTIES — Extended Property Details

The companion view VW_DATASET_METADATA_OBJECT_PROPERTIES provides a vertical (one-row-per-property) representation of detailed metadata attributes for schemas, tables, and columns. This view surfaces custom field values, discussion comments, lineage definitions, linked tests, and expanded schema metadata that don't appear in the flat VW_DATASET_METADATA_OBJECTS view.

Schema

Property Categories

Joining Metadata Objects to Properties

Use OBJECT_KEY or OBJECT_ID + OBJECT_TYPE as the join key. For joining to VW_DATASET_COLUMNS, use the pattern concat('c.', COLUMN_ID) to build the OBJECT_KEY.

Example: Columns with a Discussion Comment Containing "security"

Find columns where a user has added a comment mentioning "security", filtered to STRING data types:

SELECT
    prop_comment.DATA_SOURCE_NAME,
    prop_comment.METADATA_PATH,
    prop_comment.PROPERTY_VALUE AS COMMENT_TEXT,
    prop_comment.MODIFIED_BY,
    prop_comment.DATE_MODIFIED
FROM REPOSITORY.VW_DATASET_METADATA_OBJECT_PROPERTIES prop_comment
INNER JOIN REPOSITORY.VW_DATASET_METADATA_OBJECT_PROPERTIES prop_type
    ON prop_type.OBJECT_ID = prop_comment.OBJECT_ID
    AND prop_type.OBJECT_TYPE = 'Column'
    AND prop_type.PROPERTY_CATEGORY = 'Expanded Schema Metadata'
    AND prop_type.PROPERTY_NAME = 'Data Type'
    AND UPPER(prop_type.PROPERTY_VALUE) LIKE '%STRING%'
WHERE prop_comment.OBJECT_TYPE = 'Column'
    AND prop_comment.PROPERTY_CATEGORY = 'Discussion Comment'
    AND LOWER(prop_comment.PROPERTY_VALUE) LIKE '%security%'
ORDER BY prop_comment.DATE_MODIFIED DESC;

Example: Tables with a Custom Field "Data Owner" Set to a Specific Team

SELECT
    p.DATA_SOURCE_NAME,
    p.METADATA_PATH AS TABLE_PATH,
    p.PROPERTY_VALUE AS DATA_OWNER,
    p.DATE_MODIFIED
FROM REPOSITORY.VW_DATASET_METADATA_OBJECT_PROPERTIES p
WHERE p.OBJECT_TYPE IN ('Table', 'View')
    AND p.PROPERTY_CATEGORY = 'Custom Metadata'
    AND p.PROPERTY_NAME = 'Data Owner'
    AND p.PROPERTY_VALUE = 'Analytics Engineering'
ORDER BY p.DATA_SOURCE_NAME, p.METADATA_PATH;

Example: Columns That Are Primary Keys with No Linked Tests

SELECT
    pk.DATA_SOURCE_NAME,
    pk.METADATA_PATH,
    pk.OBJECT_URL
FROM REPOSITORY.VW_DATASET_METADATA_OBJECT_PROPERTIES pk
WHERE pk.OBJECT_TYPE = 'Column'
    AND pk.PROPERTY_CATEGORY = 'Expanded Schema Metadata'
    AND pk.PROPERTY_NAME = 'Is Primary Key'
    AND pk.PROPERTY_VALUE = 'Yes'
    AND NOT EXISTS (
        SELECT 1
        FROM REPOSITORY.VW_DATASET_METADATA_OBJECT_PROPERTIES lt
        WHERE lt.OBJECT_ID = pk.OBJECT_ID
            AND lt.OBJECT_TYPE = 'Column'
            AND lt.PROPERTY_CATEGORY = 'Linked Tests'
    )
ORDER BY pk.DATA_SOURCE_NAME, pk.METADATA_PATH;

Example: All Lineage Sources for a Specific Table

SELECT
    p.PROPERTY_NAME,
    p.PROPERTY_VALUE AS SOURCE_OBJECT,
    p.MODIFIED_BY,
    p.DATE_MODIFIED
FROM REPOSITORY.VW_DATASET_METADATA_OBJECT_PROPERTIES p
WHERE p.OBJECT_TYPE = 'Table'
    AND p.METADATA_PATH = 'PUBLIC.DIM_CUSTOMER'
    AND p.PROPERTY_CATEGORY = 'Lineage Definition'
ORDER BY p.PROPERTY_NAME;

Example: Find All Columns of a Specific Data Type Across All Data Sources

SELECT
    p.DATA_SOURCE_NAME,
    p.METADATA_PATH,
    p.PROPERTY_VALUE AS DATA_TYPE
FROM REPOSITORY.VW_DATASET_METADATA_OBJECT_PROPERTIES p
WHERE p.OBJECT_TYPE = 'Column'
    AND p.PROPERTY_CATEGORY = 'Expanded Schema Metadata'
    AND p.PROPERTY_NAME = 'Data Type'
    AND UPPER(p.PROPERTY_VALUE) LIKE '%TIMESTAMP%'
ORDER BY p.DATA_SOURCE_NAME, p.METADATA_PATH;

Secure View

VW_SECURE_DATASET_METADATA_OBJECT_PROPERTIES adds USER_ID and ACCESS_TYPE columns, filtered by data source ViewMetadata permission (same security tier as VW_SECURE_DATASET_METADATA_OBJECTS).

Databricks Notes

• Replace ILIKE with LOWER(OBJECT_NAME) LIKE LOWER('%customer%') if your Databricks configuration is case-sensitive
• Subquery syntax in the hierarchy example works the same in both engines, though a JOIN-based approach may perform better on large catalogs