How to get the actual script of a stored procedure in another server?

Question

I use SQL Server 2017. I have linked a server\database to another my main database. So, if I execute the query below, it works and brings me the correct data:

use [myMainServer].[myMainDatabase] GO

...
Select * from [myOtherServer].[myOtherDatabase].dbo.[myTable]

What I want to do is to get the actual script of a stored procedure from the linked database. Assume I have the stored procedure: sp_GetNumbers, then I can receive its content if I execute the code below in the linked server itself:

SELECT OBJECT_DEFINITION(OBJECT_ID('sp_GetNumbers'))

However I couldn't manage to do it from my main database. I've tried below but does not work.

SELECT OBJECT_DEFINITION(OBJECT_ID('[myOtherServer].[myOtherDatabase].sp_GetNumbers'))

My question is: How can I get the script of a Stored Procedure in SQL server B ([myOtherServer].[myOtherDatabase]) by running a query in SQL server A ([myMainServer].[myMainDatabase])?

Answer 1

The object functions are context specific so that won't work, but you could use the system views. Something like this:

SELECT [definition] 
from [myOtherServer].[myOtherDatabase].sys.sql_modules m 
inner join [myOtherServer].[myOtherDatabase].sys.objects o
on m.[object_id] = o.[object_id]
where o.[name] = 'sp_GetNumbers'