Wednesday, 20 April 2016

Oracle SQL Query to find the number of columns in a table.

Requirement: Write a query to find the number of columns in a table in Oracle SQL.

Solution: For solution of the above requirement, we will use the DEPARTMENTS table present in the HR Schema of the Oracle Database 11g XE.

1. Querying  "ALL_TAB_COLUMN ": 

ALL_TAB_COLUMNS describes the columns of the tables, views, and clusters accessible to the current user.

SELECT COUNT(*) TOTAL_COLOUMNS
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME='DEPARTMENTS';


2. Querying  "USER_TAB_COLUMN ": 

USER_TAB_COLUMNS describes the columns of the tables, views, and clusters owned by the current user. Its columns (except for OWNER) are the same as those in "ALL_TAB_COLUMNS".

SELECT COUNT(*) TOTAL_COLOUMNS
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME='DEPARTMENTS';



Thus, the solution to our requirement.

If you like the post please comment, share, and like me on Facebook.


Thanks & Regards,

Susanto Paul