如何在Vertica中查找表的创建者（已变更表所有者）

If you’ve already run ALTER TABLE t33 OWNER TO Bob; and don’t have access to the original CREATE TABLE logs, don’t worry—Vertica’s system catalog keeps track of the original creator separately from the current table owner. Here’s how to retrieve that info:

1. Query the v_catalog.tables System View #

This is the most reliable method, as the catalog stores persistent metadata about your tables—including the original creator, even after ownership changes. Run this SQL query:

SELECT creator_name
FROM v_catalog.tables
WHERE table_name = 't33'
  AND table_schema = 'your_schema_name'; -- Replace with your actual schema (e.g., public)

• creator_name: This column explicitly holds the username of the user who initially created the table, regardless of any subsequent ALTER TABLE OWNER operations.
• Always specify table_schema if your table isn’t in the default public schema—this avoids returning duplicate results if there are tables named t33 in different schemas.

2. Fallback: Check v_monitor.table_creation_events (If Available) #

If for some edge case the v_catalog.tables entry doesn’t have the info (unlikely, but possible), you can check the monitor view that tracks table creation events:

SELECT creator_name, create_time
FROM v_monitor.table_creation_events
WHERE table_name = 't33'
  AND table_schema = 'your_schema_name';

Note that v_monitor data has a retention period (configured by your Vertica admin), so this might not have records if the table was created a long time ago. Stick with v_catalog.tables as your primary source.

Permission Note #

You’ll need SELECT access on the system views mentioned above. If you don’t have permission, reach out to your Vertica database administrator to run the query for you.

内容的提问来源于stack exchange，提问作者Jeremy Hunts