Table information

[desi5pure]

If I know a table name, how would I know what columns are used as primaryt key. 'Desc Table_name' doesn't provide that info.
What system table I can check?

 

[Ascalonian]

You can do this:

SELECT column_name, nullable
FROM all_tab_columns
WHERE table_name LIKE 'TABLE_NAME%%'
ORDER BY column_name

Nullable specifies whether a column allows NULLs. Value is N if there is a NOT NULL constraint on the column or if the column is part of a PRIMARY KEY.

Hope this helps!

If not, I would recommend getting an IDE like Quest TOAD.

 

[BJCooperIT]

Just knowing whether a column has a "not null" constraint will not tell you if it is the primary key. Take for instance an employee table. Its primary key might be a unique sequence number, but the name column may also be a mandatory column. Both would show in the select from all_tab_columns.

If your DBA used constraints to identify primary keys, try instead:

SELECT *
FROM   sys.all_constraints
WHERE  table_name = 'MY_TABLE'
AND    constraint_type = 'P'

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[desi5pure]

I did execute following query but it shows constraint name; is there a way to see fields/columns used for that constraint or primary key?
SELECT *
FROM sys.all_constraints
WHERE table_name = 'MY_TABLE'
AND constraint_type = 'P'

 

[BJCooperIT]

See if this works for you:

SELECT *
FROM   sys.all_ind_columns aic,
       sys.all_constraints ac
WHERE  aic.table_owner = 'TABLE_SCHEMA'
AND    ac.table_name = aic.table_name
AND    ac.constraint_type = 'P'
AND    ac.table_name = 'MY_TABLE'
AND    ac.index_name = aic.index_name
ORDER BY aic.table_owner, aic.table_name, aic.index_name, aic.column_position

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com