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

Posted: February 18, 2009 in Oracle, Programming Language
Tags: , , , ,

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
where a.TABLE_NAME=u.TABLE_NAME
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.

About these ads
Comments
  1. Mike says:

    Just passing by.Btw, your website have great content!

    _________________________________
    Making Money $150 An Hour

  2. vijvipin says:

    Thanks Mike for your Visit and for the words of Appreciation :)

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

  3. Joel says:

    I guess my question is, why even use the USER_ALL_TABLES table?

    But yeah, I do love me some system views!

    • vijvipin says:

      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%’

      Result:
      OWNER TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH

      SYS ALL_ALL_TABLES empid VARCHAR2 3
      SYS ALL_APPLY_DML_HANDLERS empid VARCHAR2 92
      SYS ALL_DB_LINKS empid VARCHAR2 30
      SYS ALL_INDEXES empid VARCHAR2 3

      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

  4. Joel says:

    Ah, but all_tab_columns has an OWNER column as well

  5. Joel says:

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

    • vijvipin says:

      Hi Joel,
      visited your site http://myzenarcade.com. 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 :)

  6. Joel says:

    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

  7. Joel says:

    just checking in, did that help?
    If not, you can send me whatever you are working on and I can send something back

    • vijvipin says:

      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

  8. Joel says:

    sure, drop me an email with your email and I’ll send it your way.

  9. Ashok Vardhan Sanda says:

    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?

    • vijvipin says:

      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.

  10. Daren says:

    Can you do a similar search in all views? i want to look in all views for a column named ‘latitude’

    • vijvipin says:

      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;

  11. Ofr says:

    Hi,

    How can I search for a column in all user VIEWS?

    Thanks!

  12. Ofr says:

    How can I search for a column in all user VIEWS?

    Thanks!

    • vijvipin says:

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

      select
      DISTINCT(name)from user_source
      where
      type=’VIEW’
      and
      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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s