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

Need Source for Information_Schema Views

You can find the source code for these views in the master database...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:DD2349E8-3160-478A-9ED5-1E9CB7B37FA0@.microsoft.com...
>I need the source definitions for the Information_Schema.Routines view and
> would probably find a use for other view sources from time to time. Can
> anyone point me to these?
> I am trying to reverse engineer a poorly documented SQL Server 2000 DB wit
h
> EXTENSIVE business logic in hundreds of sometimes large SPs. The
> Information_Schema.Routines view only allows 4000 characters for the routi
ne
> definition column (the source code for the procedure) and I have lots and
> lots of SPs over that limit. I'm trying to create a report containing key
> info about key procedures but I'm having content chopped off.
> Thanks. Larry
> --
> Larry WestHi Larry
Can you clarify?
Is your need actually to get the definition of the
Information_Schema.Routines view, or is to find a way to display the text of
your own procedures that are are more than 4000 characters in length?
You can use sp_helptext 'my_proc_name' to get the full text of any
procedure. You can also use it to get the full text of any view, so you
could go to the master database and do this
exec sp_helptext 'information_schema.routines'
Or, in your own user databases, you could do this:
EXEC sp_helptext 'mybigprocedure'
Also note that in certain tools, like the Query Analyzer, the maximum number
of display characters is by default set to a small number. You would see the
full definition, even if it is available, if you don't go to
Tools|Options|Results and change the value of Maximum characters per column.
HTH
Kalen Delaney, SQL Server MVP
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:DD2349E8-3160-478A-9ED5-1E9CB7B37FA0@.microsoft.com...
>I need the source definitions for the Information_Schema.Routines view and
> would probably find a use for other view sources from time to time. Can
> anyone point me to these?
> I am trying to reverse engineer a poorly documented SQL Server 2000 DB
> with
> EXTENSIVE business logic in hundreds of sometimes large SPs. The
> Information_Schema.Routines view only allows 4000 characters for the
> routine
> definition column (the source code for the procedure) and I have lots and
> lots of SPs over that limit. I'm trying to create a report containing key
> info about key procedures but I'm having content chopped off.
> Thanks. Larry
> --
> Larry West|||I need the source definitions for the Information_Schema.Routines view and
would probably find a use for other view sources from time to time. Can
anyone point me to these?
I am trying to reverse engineer a poorly documented SQL Server 2000 DB with
EXTENSIVE business logic in hundreds of sometimes large SPs. The
Information_Schema.Routines view only allows 4000 characters for the routine
definition column (the source code for the procedure) and I have lots and
lots of SPs over that limit. I'm trying to create a report containing key
info about key procedures but I'm having content chopped off.
Thanks. Larry
--
Larry West|||You can find the source code for these views in the master database...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:DD2349E8-3160-478A-9ED5-1E9CB7B37FA0@.microsoft.com...
>I need the source definitions for the Information_Schema.Routines view and
> would probably find a use for other view sources from time to time. Can
> anyone point me to these?
> I am trying to reverse engineer a poorly documented SQL Server 2000 DB wit
h
> EXTENSIVE business logic in hundreds of sometimes large SPs. The
> Information_Schema.Routines view only allows 4000 characters for the routi
ne
> definition column (the source code for the procedure) and I have lots and
> lots of SPs over that limit. I'm trying to create a report containing key
> info about key procedures but I'm having content chopped off.
> Thanks. Larry
> --
> Larry West|||Hi Larry
Can you clarify?
Is your need actually to get the definition of the
Information_Schema.Routines view, or is to find a way to display the text of
your own procedures that are are more than 4000 characters in length?
You can use sp_helptext 'my_proc_name' to get the full text of any
procedure. You can also use it to get the full text of any view, so you
could go to the master database and do this
exec sp_helptext 'information_schema.routines'
Or, in your own user databases, you could do this:
EXEC sp_helptext 'mybigprocedure'
Also note that in certain tools, like the Query Analyzer, the maximum number
of display characters is by default set to a small number. You would see the
full definition, even if it is available, if you don't go to
Tools|Options|Results and change the value of Maximum characters per column.
HTH
Kalen Delaney, SQL Server MVP
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:DD2349E8-3160-478A-9ED5-1E9CB7B37FA0@.microsoft.com...
>I need the source definitions for the Information_Schema.Routines view and
> would probably find a use for other view sources from time to time. Can
> anyone point me to these?
> I am trying to reverse engineer a poorly documented SQL Server 2000 DB
> with
> EXTENSIVE business logic in hundreds of sometimes large SPs. The
> Information_Schema.Routines view only allows 4000 characters for the
> routine
> definition column (the source code for the procedure) and I have lots and
> lots of SPs over that limit. I'm trying to create a report containing key
> info about key procedures but I'm having content chopped off.
> Thanks. Larry
> --
> Larry West

Need Source for Information_Schema Views

I need the source definitions for the Information_Schema.Routines view and
would probably find a use for other view sources from time to time. Can
anyone point me to these?
I am trying to reverse engineer a poorly documented SQL Server 2000 DB with
EXTENSIVE business logic in hundreds of sometimes large SPs. The
Information_Schema.Routines view only allows 4000 characters for the routine
definition column (the source code for the procedure) and I have lots and
lots of SPs over that limit. I'm trying to create a report containing key
info about key procedures but I'm having content chopped off.
Thanks. Larry
--
Larry WestYou can find the source code for these views in the master database...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:DD2349E8-3160-478A-9ED5-1E9CB7B37FA0@.microsoft.com...
>I need the source definitions for the Information_Schema.Routines view and
> would probably find a use for other view sources from time to time. Can
> anyone point me to these?
> I am trying to reverse engineer a poorly documented SQL Server 2000 DB with
> EXTENSIVE business logic in hundreds of sometimes large SPs. The
> Information_Schema.Routines view only allows 4000 characters for the routine
> definition column (the source code for the procedure) and I have lots and
> lots of SPs over that limit. I'm trying to create a report containing key
> info about key procedures but I'm having content chopped off.
> Thanks. Larry
> --
> Larry West|||Hi Larry
Can you clarify?
Is your need actually to get the definition of the
Information_Schema.Routines view, or is to find a way to display the text of
your own procedures that are are more than 4000 characters in length?
You can use sp_helptext 'my_proc_name' to get the full text of any
procedure. You can also use it to get the full text of any view, so you
could go to the master database and do this
exec sp_helptext 'information_schema.routines'
Or, in your own user databases, you could do this:
EXEC sp_helptext 'mybigprocedure'
Also note that in certain tools, like the Query Analyzer, the maximum number
of display characters is by default set to a small number. You would see the
full definition, even if it is available, if you don't go to
Tools|Options|Results and change the value of Maximum characters per column.
--
HTH
Kalen Delaney, SQL Server MVP
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:DD2349E8-3160-478A-9ED5-1E9CB7B37FA0@.microsoft.com...
>I need the source definitions for the Information_Schema.Routines view and
> would probably find a use for other view sources from time to time. Can
> anyone point me to these?
> I am trying to reverse engineer a poorly documented SQL Server 2000 DB
> with
> EXTENSIVE business logic in hundreds of sometimes large SPs. The
> Information_Schema.Routines view only allows 4000 characters for the
> routine
> definition column (the source code for the procedure) and I have lots and
> lots of SPs over that limit. I'm trying to create a report containing key
> info about key procedures but I'm having content chopped off.
> Thanks. Larry
> --
> Larry Westsql