Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query to get related Table name from given Column name in Oracle SQL Developer Expand / Collapse
Author
Message
Posted Tuesday, July 9, 2013 3:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:36 AM
Points: 92, Visits: 117
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.
Post #1471512
Posted Tuesday, July 9, 2013 4:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:01 AM
Points: 5,312, Visits: 9,716
I'm not sure what you mean, but have you tried the INFORMATION_SCHEMA.COLUMNS view?

John
Post #1471517
Posted Tuesday, July 9, 2013 4:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:36 AM
Points: 92, Visits: 117
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
Post #1471520
Posted Tuesday, July 9, 2013 8:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:03 PM
Points: 13,289, Visits: 12,136
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1471654
Posted Tuesday, July 9, 2013 9:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:48 PM
Points: 12,908, Visits: 32,005
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1471689
Posted Friday, July 12, 2013 6:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:36 AM
Points: 92, Visits: 117
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


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%')



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")


Post #1472995
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse