I am trying to fetch column names form Table in Oracle. But I am not getting Column Names. I used Many query's, And Find may query's in Stack overflow But I didn't get answer.
I used below query's:
1. SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME='TABLE_NAME';
2. SELECT COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME='TABLE_NAME';
But Out Put is
no row selected
What is the problem here. Thank you very much
both of the queries are correct, just the thing which can cause this problem is that ,maybe you did n't write your table name with capital letters you must do something like this:
SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS
where TABLE_NAME = UPPER('TABLE_NAME');
OR
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS
where TABLE_NAME = UPPER('TABLE_NAME');
Try this:
SELECT column_name
FROM all_tab_cols
WHERE upper(table_name) = 'TABLE_NAME'
AND owner = ' || +_db+ || '
AND column_name NOT IN ( 'password', 'version', 'id' )
or
SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS where TABLE_NAME = UPPER('TABLE_NAME');
upper(table_name) = 'TABLE_NAME'
I hope This query would help yu out
SELECT SCHEMA_NAME (schema_id) + '.' + t.name AS 'Table Name'
FROM sys.tables t
INNER JOIN sys.columns c
ON c.object_id = t.object_id
WHERE c.name like '%hmy%
ORDER BY 'Table Name'
