CODEWITHSUNDEEP
oracle-databaseoracle11goracle10gconstraintsnotnull
Sarath Subramanian
Sarath Subramanian

Reputation: 21311

How to get all not null columns in a table

I have a requirement to find all not-null columns in a table. For example, my table is the below one

enter image description here

Lets say, Column1, Column2 and Column3 have not-null constraints and Column4, Column5 and Column6 are of nullable types. Is there any query in Oracle that list the column names that are of not-null types, ie I need to get the column names Column1, Column2 and Column3.

DESIRED OUTPUT

Column1
Column2
Column3

I know there should be a simple way to achieve this, but am new to Oracle. Any help would be highly appreciated.

Upvotes: 1

Views: 9962

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

I know there should be a simple way to achieve this, but am new to Oracle.

Well, online documentation is exactly what you need to look into.

Depending on the privilege, you need to look into [DBA|USER|ALL]_TAB_COLUMNS.

ALL_TAB_COLUMNS

Column      Datatype        Description
NULLABLE    VARCHAR2(1)     Indicates whether a column allows NULLs. 
                            The value is N if there is a NOT NULL constraint
                            on the column or if the column is part of a PRIMARY KEY. 
                            The constraint should be in an ENABLE VALIDATE state.

So, per the documentation, you need to use the filter:

NULLABLE = 'N'

Upvotes: 3

gmiley
gmiley

Reputation: 6604

You can query the all_tab_columns table:

select column_name
from all_tab_columns
where table_name = 'TABLE1'
and nullable = 'N';

Upvotes: 12

Related Questions