Unity Catalog information_schema cheat sheet: find tables, owners, columns, grants and tags with SQL

#When to use this

Someone asks "which tables have a column called email?", "who owns this schema?" or "who can read this table?". You could click through Catalog Explorer, but a query against information_schema answers the question for a whole catalog at once. You can also save the query, schedule it or export the results.

Every Unity Catalog catalog has its own information_schema. The system.information_schema schema covers all catalogs in the metastore.

Tip

Results are filtered by your privileges. You only see objects you can access, and you don't need a SELECT grant on information_schema itself. If a table is "missing" from a query, check your grants before you assume it doesn't exist.

Two more limits to know:

• information_schema doesn't include hive_metastore objects. Use SHOW TABLES IN hive_metastore.<schema> for those.
• Users with MANAGE on an object see only their own grants in table_privileges. Use SHOW GRANTS to see all of them.

#Catalogs, schemas and owners

-- Catalogs and owners
SELECT catalog_name, catalog_owner, created, created_by, comment
FROM system.information_schema.catalogs
ORDER BY catalog_name;

-- Schemas in one catalog, newest first
SELECT schema_name, schema_owner, created, created_by, last_altered
FROM main.information_schema.schemata
ORDER BY created DESC;

-- Objects owned by individual users rather than groups
-- (adjust the pattern to your naming)
SELECT table_catalog, table_schema, table_name, table_owner
FROM system.information_schema.tables
WHERE table_owner LIKE '%@%'
ORDER BY table_owner;

The last query is useful before someone leaves the company. Objects owned by a person become a problem when their account is removed. Transfer ownership to a group:

ALTER TABLE main.sales.orders OWNER TO `data-engineering`;
ALTER SCHEMA main.sales OWNER TO `data-engineering`;

#Tables

-- Table count by type and format per catalog
SELECT table_catalog, table_type, data_source_format, COUNT(*) AS tables
FROM system.information_schema.tables
WHERE table_schema <> 'information_schema'
GROUP BY ALL
ORDER BY table_catalog, tables DESC;

-- External tables and where their files live
SELECT table_catalog, table_schema, table_name, storage_path
FROM system.information_schema.tables
WHERE table_type = 'EXTERNAL'
ORDER BY storage_path;

-- Tables not altered in 180 days (candidates for review, not for blind deletion)
SELECT table_catalog, table_schema, table_name, table_owner, last_altered
FROM system.information_schema.tables
WHERE last_altered < current_timestamp() - INTERVAL 180 DAYS
  AND table_type IN ('MANAGED', 'EXTERNAL')
ORDER BY last_altered;

-- Recently created or changed objects, and by whom
SELECT table_catalog, table_schema, table_name, table_type, created_by, last_altered_by, last_altered
FROM system.information_schema.tables
WHERE last_altered > current_timestamp() - INTERVAL 1 DAY
ORDER BY last_altered DESC;

Caution

last_altered tracks metadata and write changes. It doesn't track reads. A table that hasn't been altered can still feed a daily dashboard. Before you drop anything, check reads in lineage or audit data. See impact analysis with Unity Catalog lineage system tables.

#Columns

-- Find every table with a column that looks like personal data
SELECT table_catalog, table_schema, table_name, column_name, full_data_type
FROM system.information_schema.columns
WHERE lower(column_name) RLIKE 'email|phone|ssn|birth|iban'
ORDER BY 1, 2, 3;

-- Full column list for one table, in order
SELECT ordinal_position, column_name, full_data_type, is_nullable, comment
FROM main.information_schema.columns
WHERE table_schema = 'sales' AND table_name = 'orders'
ORDER BY ordinal_position;

-- Same column name, different types across tables (join and union bugs)
SELECT column_name, COLLECT_SET(full_data_type) AS types, COUNT(*) AS tables
FROM main.information_schema.columns
WHERE table_schema = 'sales'
GROUP BY column_name
HAVING size(COLLECT_SET(full_data_type)) > 1;

-- Tables with no column comments at all
SELECT table_schema, table_name
FROM main.information_schema.columns
GROUP BY table_schema, table_name
HAVING COUNT(comment) = 0;

#Grants

-- Who can do what on one table (includes inherited privileges)
SELECT grantee, privilege_type, inherited_from, grantor
FROM main.information_schema.table_privileges
WHERE table_schema = 'sales' AND table_name = 'orders'
ORDER BY grantee;

-- Everything one group has been granted in a catalog
SELECT 'catalog' AS level, catalog_name AS object, privilege_type FROM system.information_schema.catalog_privileges
  WHERE grantee = 'analysts' AND catalog_name = 'main'
UNION ALL
SELECT 'schema', concat_ws('.', catalog_name, schema_name), privilege_type FROM system.information_schema.schema_privileges
  WHERE grantee = 'analysts' AND catalog_name = 'main'
UNION ALL
SELECT 'table', concat_ws('.', table_catalog, table_schema, table_name), privilege_type FROM system.information_schema.table_privileges
  WHERE grantee = 'analysts' AND table_catalog = 'main';

-- Grants made directly to users instead of groups
SELECT table_catalog, table_schema, table_name, grantee, privilege_type
FROM system.information_schema.table_privileges
WHERE grantee LIKE '%@%';

inherited_from shows whether a privilege comes from the table itself or from its schema or catalog. Revoke a privilege at the level it was granted. Revoking it on the table does nothing if the grant sits on the schema.

To check one object quickly, SHOW GRANTS ON TABLE main.sales.orders; returns the same data. For fixing "permission denied", see the Unity Catalog grants cheat sheet.

#Tags, masks and row filters

-- Tables tagged as containing PII
SELECT catalog_name, schema_name, table_name, tag_value
FROM system.information_schema.table_tags
WHERE tag_name = 'pii';

-- Tagged columns that have no column mask (protection gap)
SELECT t.catalog_name, t.schema_name, t.table_name, t.column_name, t.tag_name
FROM system.information_schema.column_tags t
LEFT JOIN system.information_schema.column_masks m
  ON  m.catalog_name = t.catalog_name AND m.schema_name = t.schema_name
  AND m.table_name = t.table_name    AND m.column_name = t.column_name
WHERE t.tag_name = 'pii' AND m.mask_name IS NULL;

-- Tables with row filters, and the function used
SELECT catalog_name, schema_name, table_name,
       concat_ws('.', filter_catalog, filter_schema, filter_name) AS filter_function
FROM system.information_schema.row_filters;

Tag names here (pii) are examples. Use your organization's tag names. If you use governed tags and ABAC policies, protection can come from a policy instead of a per-column mask, so the gap query can over-report. See data classification in Unity Catalog and row filters and column masks.

#Views, functions, volumes and external locations

-- Views that reference a table you plan to change
SELECT table_catalog, table_schema, table_name
FROM system.information_schema.views
WHERE view_definition ILIKE '%sales.orders%';

-- SQL and Python functions per schema
SELECT routine_catalog, routine_schema, routine_name, routine_body, external_language, created_by
FROM system.information_schema.routines
ORDER BY 1, 2, 3;

-- Volumes and their storage
SELECT volume_catalog, volume_schema, volume_name, volume_type, volume_owner, storage_location
FROM system.information_schema.volumes;

-- External locations, credentials and read-only flag
SELECT external_location_name, url, storage_credential_name, read_only, external_location_owner
FROM system.information_schema.external_locations
ORDER BY url;

The view_definition search is a text match. It misses views that reach the table through another view, and it misses references written without the schema prefix. Use lineage for a complete answer.

#Quick reference

#Prevent it next time

• Save the "owned by a user" and "granted directly to a user" queries as a weekly scheduled query with an alert when rows are returned.
• Run the PII-without-mask query after every release that adds columns.
• Run inventory queries as an admin or a service principal with broad USE privileges. Results from a regular user are incomplete by design.
• Keep hive_metastore in mind. Anything still there is invisible to these queries until you migrate it.