June 7, 2013 at 8:15 am
Hi,
I have an access to oracle linked server called [oracleLS]
it is there under the SERVER OBJECT => LINKED SERVER => [ORACLE LS] => [CATALOG] => [DEFAUL] => [TABLES] => AND LIST OF TABLES WITH THEIR SCHEMA NAME.TABLES NAMES.
I want to find out list of tables from these tables which has specific column called 'business address';
I USED FOLLOWING ONE..BUT GIVES ME ERROR OF INVALID OBJECT.
SELECT
TABLE_NAME,
COLUMN_NAME
FROM
ALL_TAB_COLUMNS
WHERE
COLUMN_NAME LIKE '%PATTERN%';
I AM ALSO WONDERING WHERE I HAVE TO SPECIFY THE LINKED SERVER NAME IN ABOVE QUERY!!
I would also like to try another option if you have any idea!!
Please help me.
thanks.
June 7, 2013 at 8:20 am
it's incredsibly similar; you just have to use the four part naming convention:
SELECT
TABLE_NAME,
COLUMN_NAME
FROM
MyOracleLinkedServer...ALL_TAB_COLUMNS
WHERE
COLUMN_NAME LIKE '%PATTERN%';
if you are not a sysadmin on the linked server, you might have to use USER_TAB_COLUMNS instead.
Lowell
June 7, 2013 at 8:20 am
Hi,
select OBJECT_NAME(object_id),* from linkedserver.master.sys.columns where name like '%%'
June 7, 2013 at 8:26 am
WHEN I tried folowing query..
select OBJECT_NAME(object_id),* from [13.137.16.51].master.sys.columns where name like 'LAST%'
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "OraOLEDB.Oracle" for linked server "10.197.6.61". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
Is it something related to previllages?
need a help!
June 7, 2013 at 8:28 am
when i tried this one , it gives me different error,
SELECT
TABLE_NAME,
COLUMN_NAME
FROM
[13.137.16.51]...USER_TAB_COLUMNS
WHERE
COLUMN_NAME LIKE 'L%';
the error is
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "13.137.16.51" does not contain the table "USER_TAB_COLUMNS". The table either does not exist or the current user does not have permissions on that table.
June 7, 2013 at 8:43 am
I am just wondering about the the tables as they are located as following structre in SQL SERVER MANAGMENT STUDIO
SERVER OBJECT => LINKED SERVER => [ORACLE LS] => [CATALOG] => [DEFAUL] => [TABLES] => AND LIST OF TABLES WITH THEIR SCHEMA NAME.TABLES NAMES
THERE IS NO MASTER.SYS. IN THIS STRUCTURE, IS THAT THE REASON FOR THE ERROR!!!
June 7, 2013 at 10:00 am
prtk.raval (6/7/2013)
I am just wondering about the the tables as they are located as following structre in SQL SERVER MANAGMENT STUDIOSERVER OBJECT => LINKED SERVER => [ORACLE LS] => [CATALOG] => [DEFAUL] => [TABLES] => AND LIST OF TABLES WITH THEIR SCHEMA NAME.TABLES NAMES
THERE IS NO MASTER.SYS. IN THIS STRUCTURE, IS THAT THE REASON FOR THE ERROR!!!
master.sys.columns would only exist on a SQL 2005 thru SQL2012 linked server; the advice does not apply at all in your case , since your linked server is Oracle based.
if your linked server is working, meaning you can query specific tables, or run EXEC sp_tables_ex [myOracleLinkedServer] and get results, you will have to get with the oracle administrator to give your ORACLE login permissions to see the user_Tab_columns view, and similar metadata views.
Lowell
June 7, 2013 at 12:34 pm
Hi finally I manage to get it done with following query.
select * from [13.137.16.51]..SYS.ALL_TAB_COLS
where owner = 'PBR'
and column_name = 'LASTWRITTEN'
June 7, 2013 at 12:44 pm
Now I am trying to get latest update or last modified timestamp, which itsef is "LASTWRITTEN" from each table.
when I tried above query it give me list of tables..which are more than 1450.
I came to know that with the following query I can achive this objective for one table.
select max(lastwritten) from [13.137.16.51]..DBO.ADDRESS
Now my issue is how to get this solution for each 1450 tables
I am getting 1450 tables from above query, because they have "LASTWRITTEN" columns.
I want to get max(lastwritten) from each of this table in one or two go , instead of writing it for 1450times.
Please Help Me.
Thanks.
June 7, 2013 at 2:32 pm
here's two ways how i might do it.
first create yourself a temp table to capture the results from the code below:
the code below is doing TWO examples,
one to build 1450 selperate linked server queries to copy and paste;
the other to build the core of the SQL for a view to create in Oracle itself.
Idea1:
CREATE Table #tmp(TableName Varchar(128),lastWritten datetime )
use the metadata to generate your 1450 commands.
run this, and copy and paste the results of Column 1 into SSMS:
SELECT
'INSERT INTO #TMP(TableName,lastWritten) SELECT '''+ TABLE_NAME +'''As Tbl, MAX(LASTWRITTEN) AS LastWritten FROM [13.137.16.51]..DBO.' + TABLE_NAME +';' AS CMD,
'SELECT '''+ TABLE_NAME +'''As Tbl, MAX(LASTWRITTEN) AS LastWritten FROM ' + TABLE_NAME + ' UNION ALL' As Cmd2
FROM [13.137.16.51]..SYS.ALL_TAB_COLS
WHERE OWNER = 'PBR'
AND COLUMN_NAME = 'LASTWRITTEN'
now you can do it like that, or you can the results of Column 2 in this query use that list of tables, to create a view on oracle that gathers that same info,
note there will be a trailing UNION ALL that you must remove in order to run the results:
it'd be a lot easier if the view gathered it on the Oracle Side, instead of 1450 Linked Server Queries., then you could have something that looked like this in oracle:
CREATE OR REPLACE VIEW VW_LASTWRITTEN
AS
SELECT 'ORDERS' As Tbl, Max(LASTWRITTEN) FROM Orders UNION ALL
--imagine releated 1448 more times for all the otehr tables
SELECT 'CUSTOMERS' As Tbl, Max(LASTWRITTEN) FROM Customers;
that makes it a zillion times easier to query from a linked Server, since it's pre-aggregated for you:
SELECT * FROM [13.137.16.51]...VW_LASTWRITTEN;
Lowell
June 7, 2013 at 3:00 pm
Hi champion, What is SSMS..where I have to copy the result of column1.?
June 7, 2013 at 3:04 pm
prtk.raval (6/7/2013)
Hi champion, What is SSMS..where I have to copy the result of column1.?
SSMS = SQL Server Management Studio, sorry about the acronym!
Lowell
June 7, 2013 at 3:20 pm
Hi Champion,
when i used your second option , I got following errorrmessage.
here studentDetails is the table name exist in oracle server.
and it is listed by your query on the top.
so when I run that creating view query , i got following error message.
when i just made comment..
it gives an error for second table name .
Msg 208, Level 16, State 1, Procedure VW_LASTWRITTEN, Line 3
Invalid object name 'studentDetails'.
June 10, 2013 at 3:05 pm
Hi,
my issue is
My table is as follows:
CREATE Table Table_Update(TableName Varchar(128) primary key,StartlastWritten datetime, EndlastWritten datetime)
When I run following query :
SELECT 'UPDATE Table_Update SET EndlastWritten= (SELECT MAX(LASTWRITTEN)
FROM [13.137.16.51]..PBR.' + TABLE_NAME +' WHERE TABLENAME='+TABLE_NAME+');'
FROM [13.137.16.51]..SYS.ALL_TAB_COLS
WHERE OWNER = 'PBR' AND COLUMN_NAME = 'LASTWRITTEN'
I am getting update statement for different tables as follows :
UPDATE Table_Update SET EndlastWritten= (SELECT MAX(LASTWRITTEN) FROM [13.137.16.51]..PBR.STUDNET WHERE TABLENAME=STUDENT);
But It gives me error due to invalid object, beacuse of inverted comma missing near the STUDENT in above example..
Actually I want to get like following query.
UPDATE Table_Update SET EndlastWritten= (SELECT MAX(LASTWRITTEN) FROM [13.137.16.51]..PBR.STUDNET WHERE TABLENAME='STUDENT');
I tried so many different ways..but coudn't workout..
Please help me.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy