[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • 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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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