0

I am using SQL Server 2008 R2,

I have access to an Oracle server from linked server with read-only access.

Now I want to get the list of tables from the Oracle server which has specific column.

For an example I want all the tables that have "Business address" column.

Please help if you have any idea / tips.!!

Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2040021
  • 309
  • 3
  • 15

2 Answers2

2

If you have the privileges and assuming that the actual name of the column is BUSINESS_ADDRESS, not a case-sensitive column name that includes a space.

SELECT owner, table_name, column_name
  FROM dba_tab_columns
 WHERE column_name = 'BUSINESS_ADDRESS'

If not, you can use ALL_TAB_COLUMNS if you only care about tables that the Oracle user you are connecting as can query or USER_TAB_COLUMNS if you only care about tables the Oracle user you are connecting as owns.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • But the issue is,..I am using SQL SERVER 2008r2 and In that I have only read-only access to the oracle server which is there in Linked server folder. can I still use your solution? – user2040021 Jun 06 '13 at 21:31
  • @user2040021 - But you said that you have a linked server. So I'm assuming you know how to run queries against Oracle via that linked server. Is that a bad assumption? – Justin Cave Jun 06 '13 at 21:33
  • I am so sorry , I made a mess. Actually I am using SQL SERVER 2008r2 , in that I have an access of Oracle Server which is there in LINKED SERVER folder of SQL SERVER 2008r2. Now the issue is I need to retrive all the tables from that oracle server that has specific column called "Business_Address". – user2040021 Jun 06 '13 at 21:35
  • @user2040021 - "read-only access" can mean lots of different things. If you have been given the privileges to read the data dictionary, then you can read from the `dba_tab_columns` table. If not, then you can only look for those tables that you have access to (or that you own) that also have the column you're interested in. You wouldn't be able to check every table in the database for the column if you haven't been given privileges to do that. – Justin Cave Jun 06 '13 at 21:39
  • WHEN I TRIED TO RUN IT LIKE AS FOLLOWS SELECT TABLE_NAME, COLUMN_NAME FROM [10.123.12.12]..ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE 'L%'; IT GIVE ME FOLLOWING ERROR Msg 208, Level 16, State 1, Line 1 Invalid object name '[10.123.12.12]..ALL_TAB_COLUMNS' – user2040021 Jun 07 '13 at 13:54
0
  • It is possible that you do not have access to some views.
  • It is possible that you do not have access to any view.

But if you have the access you can run the following SQL statements:

/* schema you are connected to */
SELECT  table_name
FROM    user_tab_columns
WHERE   LOWER(column_name) LIKE '%business%address%'
;

/* all schemas you have access to*/
SELECT  owner, table_name
FROM    all_tab_columns
WHERE   LOWER(column_name) LIKE '%business%address%'
;
  • You can create temporary table and catch results into it.
the_slk
  • 2,172
  • 1
  • 11
  • 10
  • WHEN I TRIED TO RUN IT LIKE AS FOLLOWS SELECT TABLE_NAME, COLUMN_NAME FROM [10.123.12.12]..ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE 'L%'; IT GIVE ME FOLLOWING ERROR Msg 208, Level 16, State 1, Line 1 Invalid object name '[10.123.12.12]..ALL_TAB_COLUMNS'. – user2040021 Jun 07 '13 at 13:51
  • SELECT * FROM linked_server_name..oracle_schema.oracle_table; – the_slk Jun 07 '13 at 14:39