1

I want to identify all columns within a database whose name matches with a given input column and whose datatype matches with the given input column. Can someone suggest a query for this ?

user2783058
  • 43
  • 1
  • 9
  • possible duplicate of [How can I get column names from a table?](http://stackoverflow.com/questions/452464/how-can-i-get-column-names-from-a-table) – Andreas Fester Oct 30 '14 at 07:09
  • 1
    Not quite a duplicate, but the accepted answer on that linked question is helpful here, too. – Thilo Oct 30 '14 at 07:11
  • Might be not. How will we ensure that we are getting the columns of same datatype ? – user2783058 Oct 30 '14 at 07:11
  • 1
    @user2783058 You can use my link to get started with. You should at least have tried something yourself - it is not that complicated if you read the answers from the linked question – Andreas Fester Oct 30 '14 at 07:16

2 Answers2

0

for example if i want to look for column with the name 'CELL' in table 'INT_TRN_RLDEP_CELL', then the query would be

select * from ALL_TAB_COLUMNS where table_name like 'INT_TRN_GRN_RLDEP_CELL' and COLUMN_NAME like 'CELL';

for more information visit http://en.wikipedia.org/wiki/Oracle_metadata or oracle documentation on tables metada

saikumarm
  • 1,565
  • 1
  • 15
  • 30
0

... all columns within a database whose name matches with a given input column and whose datatype matches with the given input column

You can use the all_tab_columns data dictionary view. For example, to select all columns of type NUMBER and where the column name starts with "A", use something like

SELECT table_name, column_name, data_type 
FROM all_tab_columns 
WHERE data_type='NUMBER' AND column_name like 'A%';

TABLE_NAME             COLUMN_NAME            DATA_TYPE
---------------------- ------- ------------------------------
AUDIT_ACTIONS          ACTION                 NUMBER
SDO_CS_SRS             AUTH_SRID              NUMBER
APEX_WS_APP_PAGES      APPLICATION_ID         NUMBER
...
Andreas Fester
  • 36,091
  • 7
  • 95
  • 123
  • Hi, Sorry, this may be a basic question, but I have a urgent requirement : how do we get all the columns within the table as well as other tables that match a particular column ? The result shouldn't contain the column we are comparing with. For example, as in above example, I want all columns within as well as other tables similar to A but we should not display A in the final result. – user2783058 Nov 21 '14 at 15:31