Query to get related Table name from given Column name in Oracle SQL Developer

  • Guitar_player

    SSCrazy

    Points: 2991

    Hi ALL,

    I am familiar with the SQL server 2008 R2 , but when it comes to the SQL developer i am not that much so i am facing a problem related to the creating Query .

    I am having the Tables in the Users section of a Database and i want a Query to retrieve the Table name of the ColumnName (i written inside the Query )

    Thank You.

  • John Mitchell-245523

    SSC Guru

    Points: 148314

    I'm not sure what you mean, but have you tried the INFORMATION_SCHEMA.COLUMNS view?

    John

  • Guitar_player

    SSCrazy

    Points: 2991

    John Mitchell-245523 (7/9/2013)


    I'm not sure what you mean, but have you tried the INFORMATION_SCHEMA.COLUMNS view?

    John

    Yes , I have tried using the basic names

    SELECT COLUMN_NAME, TABLE_NAME

    FROM INFORMATION_SCHEMA.COLUMNS

    but i am receiving invalid Table name or View .

    The main Problem over here is that " The Tables are not in the Direct Database , they are inside to the Individual Users (Roles) who are in that Database

  • Sean Lange

    SSC Guru

    Points: 286446

    Guitar_player (7/9/2013)


    John Mitchell-245523 (7/9/2013)


    I'm not sure what you mean, but have you tried the INFORMATION_SCHEMA.COLUMNS view?

    John

    Yes , I have tried using the basic names

    SELECT COLUMN_NAME, TABLE_NAME

    FROM INFORMATION_SCHEMA.COLUMNS

    but i am receiving invalid Table name or View .

    The main Problem over here is that " The Tables are not in the Direct Database , they are inside to the Individual Users (Roles) who are in that Database

    ummm....tables do not reside inside of a user or a role. They exist in the database, access to those tables is handled by the permissions granted to users and/or roles.

    Do you get an error when you run this?

    SELECT COLUMN_NAME, TABLE_NAME

    FROM INFORMATION_SCHEMA.COLUMNS

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Lowell

    SSC Guru

    Points: 323397

    Sean Lange (7/9/2013)


    ummm....tables do not reside inside of a user or a role. They exist in the database, access to those tables is handled by the permissions granted to users and/or roles.

    Do you get an error when you run this?

    SELECT COLUMN_NAME, TABLE_NAME

    FROM INFORMATION_SCHEMA.COLUMNS

    i think he's very oracle oriented.

    I tend to use the Oracle or SQL related views, instead of the information_schema stuff;

    so for example, in oracle, i'd query user_tab_cols; in SQL, i'd query sys.columns directly

    --ORACLE

    select * from user_tab_cols where column_name='ACTTBLKEY';

    --SQL-

    SELECT OBJECT_NAME(object_id) As TABLE_NAME,* from sys.columns WHERE name = 'ACTTBLKEY';

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Guitar_player

    SSCrazy

    Points: 2991

    Yes,

    Thanks for the guidence

    I have used Query something like this to get all the Table columns which are having nearly same name like below

    [Quote]

    select u.owner AS OWNERNAME, u.table_name as Source_TableName,u.column_name as Source_Column_Name

    from all_tab_columns u

    where Upper(u.column_name) like upper('%Colun_Name%')

    [/Quote]

    It worked fine i think.... but now i am facing another problem Please help me to find it

    Can i get a Query to know the Owner name , Table name and Column name from the Entire Database which are having same

    Employee name ("Employee1 ","Employee 2")

    Through which i can get all the owners ,tables , columns which are having that Data

    ("Employee1 ","Employee 2")

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply