Wednesday, March 28, 2012

Need to access INFORMATION_SCHEMA on linked server

Hi,
I'm working with MSSQL2K+SP3a, Standard Edition. I defined linked server (MSDE).
On the local server, I can do
select * from [testDB].[INFORMATION_SCHEMA].[TABLES] -- local

select * from [testSRV].[testDB_far].[dbo].[sysobjects] -- linked

but not

select * from [testSRV].[testDB_far].[INFORMATION_SCHEMA].[TABLES]

How can I access the INFORMATION_SCHEMA on the linked server ?

Thanks,
Helenacreate a view in linkedserver on DB which u want to see the table information and access that view from ur local server.
Note: give permission if its required to access that view.

-- create view script--
create view dbo.vINFORMATION_SCHEMATABLES
as
SELECT *
FROM INFORMATION_SCHEMA.TABLES
go

-- select statement from local server
select * from [testSRV].[testDB_far].[dbo].[vINFORMATION_SCHEMATABLES]|||Cheat?EXECUTE [testSrv].[testDB_far].dbo.sp_executesql
N'SELECT * FROM INFORMATION_SCHEMA.TABLES'-PatP|||Thanks for the fast replay .
Mallier - I cant change anything in the remote db.
Pat - you solution works perfect .

Thanks to all
Helena

No comments:

Post a Comment