?
Solved

Query Oracle System Tables

Posted on 2013-11-19
1
Medium Priority
?
477 Views
Last Modified: 2013-11-24
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
Comment
Question by:JGH5
1 Comment
 
LVL 23

Accepted Solution

by:
Steve Wales earned 2000 total points
ID: 39660891
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

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

592 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question