Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

Query Oracle System Tables

How do I search Oracle System tables to find columns with a like statement?  Below is a query I use in SQL Server 2008R2 and I need to run the equivalent Oracle system tables for the same information in Oracle 11G.  I am trying to find schema with a like statement:


SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%LastName%'   ---column search  
ORDER BY schema_name, table_name;
0
JGH5
Asked:
JGH5
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
Assuming you have DBA level permissions:

SELECT owner, table_name, column_name
FROM dba_tab_columns
WHERE column_name LIKE '%LASTNAME%'   ---column search  
ORDER BY owner, table_name; 

Open in new window


If you don't and you are connected as the owner of the table:

SELECT table_name, column_name
FROM user_tab_columns
WHERE column_name LIKE '%LASTNAME%'   ---column search  
ORDER BY table_name; 

Open in new window


(The owner column doesn't exist in the user_* views since the owner is the user you're connected as).

You could also run the first query against ALL_ instead of DBA_ - that will search all the tables that the currently connected user has access to.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now