PL/SQL -> Finding a column name in all tables

The other day, while working on a project I needed to find all the tables in a schema with a particular column name. Say for example, I wanted to list all the tables having a column name ’empid’. Following is the Oracle query to do just the same.

select a.table_name, column_name,DATA_TYPE,DATA_LENGTH from all_tab_columns a,USER_ALL_TABLES u
and column_name like ’empid%’
order by DATA_LENGTH desc;

This query will also help you to find the Datatype and length of the column name. So go explore..

Also check my post on sys_context function here. This helps to find information about the Oracle environment. Very useful.


19 thoughts on “PL/SQL -> Finding a column name in all tables

  1. Thanks Mike for your Visit and for the words of Appreciation 🙂

    Will try to continue deliver the same.. Keep visiting..

    • Hi Joel,
      First thanks for visiting and writing. 🙂

      Now, U have asked “why even use the USER_ALL_TABLES table?”. Well the answer to your query is that when I’m searching for a column name in all tables I only want “User” owned tables. If you don’t user “USER_ALL_TABLES” table, you will also get the tables whose owner is “SYS”.

      Try this

      select owner,table_name, column_name,DATA_TYPE,DATA_LENGTH from all_tab_columns
      where column_name like ’empid%’


      SYS ALL_DB_LINKS empid VARCHAR2 30

      As you see the result of the above query in the result set, the column name “OWNER” shows ‘SYS” indicating that these tables are owned by system and not user. So, if you want to search these tables (although, that would be of slim chance) you can safely remove the “USER_ALL_TABLES” table as shown above.

      I hope I was able to help you. Please do write (KEEP WRITING) in case any more query, suggestions or anything!! KEEP Learning

  2. I pretty much live in these views, using them to generate control files from external apps (like Excel) and exporting data and such.

    • Hi Joel,
      visited your site Read the articles, while going through read an article about “generate control files from external apps (like Excel)”. I tried this but could not succed, though its a very interestng an handly article. Can you elaborate on this article on “Steps to do” kind of thing so that I can do so. Thanks in advance 🙂

  3. Howdy!
    OK, well, can you tell me how far you got? Are you at all familiar with using VBA?

    I’m not positive where you are having a problem, but what you’ll need to do is:
    1. Create a form to pass in various values (noted below) or hardcode the values.
    2. The form assumes that the first row of your sheet it the column names and that they match
    the names in the database.
    3. it also assumes access to the DBA views.
    4. If you want, you can send me your sheet and I’ll make the changes so you can see.

    Changes you have to make:
    Module BuildDataload
    – You’ll either want to create a form to pass in values for inServer (sid), inUID (username) and iNPWD
    or you can hardcode them

    – You’ll notice ‘FileDir’ gets set to a location on your local drive. There is a typo here
    and it should read
    FileDir = “C:\Dataloads\” & InLoadID & “\”
    You can change this to whereever you like. I have a variable InLoadID that gets passed
    in from a form so that each of my loads gets saved toa different location.

    Function ColumnType – This function goes to the database and attempts to find the definition
    for the column in question. To make this work, you have to substitute the owner of
    the table for **WHATEVEROWNER** so that it reads owner=’ThisName’ or whatever

    • HI joel, I tried it once but could not do it. These days I am busy in some production rollouts of my couple of software, so could not try real hard on that. I’ll surely try it again and tell you. In fact is it possible for you to send me the excel with macro throuh mail.
      Thanks in advance

  4. HI, I’ve tried your column, it worked for me. I have a question. Instead of having the column_name=’emp_id’ can we check, what are all the tables that contains the column value? for. eg. if the emp_id is 12345, can we get the details of all the tables that contains this value, and also the count of records in each table?

    • Hi Ashok,
      Many thanks for reading in..
      As for your question its not possible to find all column that contain some specific value. However if you try you can build a procedure for doing this. However there is no single SQL query that can achieve this.

    • Hi Daren,
      Thanks for writing..
      And yes we can do a similar search in all views. Here is your query. Hope this helps you.. Let me know.

      select a.table_name, column_name,DATA_TYPE,DATA_LENGTH from all_tab_columns a,ALL_CATALOG u
      where a.TABLE_NAME=u.TABLE_NAME
      and column_name like upper(‘latitude%’)
      and u.table_type=’VIEW’
      and u.owner=’YOUR_OWNER’
      order by DATA_LENGTH desc;

    • Yes you can do that quite easily, Please find that below:

      DISTINCT(name)from user_source
      text like’%vUserID%’ ;

      In case you still not able to do, please let me know. I will be Happy to Help

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.