如何在Oracle SQL Developer中查找含column1的表及将其作为超键的表

Got it, let's tackle this problem step by step. You need two sets of results: first, every table that includes column1, and second, which of those tables have column1 acting as a superkey (meaning column1 alone can uniquely identify every row in the table). All using read-only SELECT statements in Oracle SQL Developer—perfect, we can use Oracle's built-in system catalog views to get this info without touching any actual data.

1. Find all tables containing column1 #

Oracle stores metadata about all accessible tables and columns in the all_tab_columns view. This query will return every table (and its owner) that has column1:

SELECT owner, table_name
FROM all_tab_columns
WHERE column_name = 'COLUMN1' -- Oracle uses uppercase by default; use '"column1"' if your column was created with quoted case-sensitive name
ORDER BY owner, table_name;

A quick note: If your column was created with case-sensitive naming (e.g., CREATE TABLE t ("column1" NUMBER);), you'll need to adjust the WHERE clause to match the exact case with double quotes inside the string.

2. Find tables where column1 is a superkey #

A superkey is a column (or set of columns) that uniquely identifies every row in a table. In Oracle, this translates to either a primary key constraint (which automatically enforces uniqueness and non-nullability) or a unique constraint (where the column must be non-null to act as a strict superkey). We also need to ensure column1 is the only column in the constraint—since we want column1 alone to be the superkey, not part of a combined key.

Here's the query:

SELECT DISTINCT c.owner, c.table_name
FROM all_constraints c
JOIN all_cons_columns cc 
  ON c.owner = cc.owner 
  AND c.constraint_name = cc.constraint_name
WHERE c.constraint_type IN ('P', 'U') -- 'P' = Primary Key, 'U' = Unique Constraint
  AND cc.column_name = 'COLUMN1'
  -- Make sure the constraint only includes column1 (not a combined key)
  AND (SELECT COUNT(*) 
       FROM all_cons_columns cc2 
       WHERE cc2.owner = c.owner 
         AND cc2.table_name = c.table_name 
         AND cc2.constraint_name = c.constraint_name) = 1
  -- Optional: Ensure column1 is non-null (strict superkey requirement)
  AND EXISTS (
      SELECT 1 
      FROM all_tab_columns tc 
      WHERE tc.owner = c.owner 
        AND tc.table_name = c.table_name 
        AND tc.column_name = 'COLUMN1' 
        AND tc.nullable = 'N'
  )
ORDER BY c.owner, c.table_name;

Let's break down the key parts:

• constraint_type IN ('P', 'U'): Primary keys are inherently superkeys. Unique constraints work as superkeys only if the column is non-null (hence the optional EXISTS check).
• The subquery checking COUNT(*) = 1 ensures we're only grabbing constraints where column1 is the sole column—so combined keys like column1 + column2 won't show up here.
• The EXISTS clause is optional: if you're okay with allowing a single null value (since Oracle's unique constraints allow one null), you can remove this part. But for a strict superkey (no nulls allowed), keep it.

Bonus: If you have DBA privileges #

If you need to check all tables in the database (including those you don't have direct access to), replace all all_* views with dba_* (e.g., dba_tab_columns, dba_constraints, dba_cons_columns).

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