sql-serverlinked-server

How do I get a list of stored procedures from a linked server?

I am trying to do a backup of all stored procedures and tables from one database server to another. I want to get a list of stored procedures and tables that exist in the destination database so I can create a script automatically from the source server to execute against the destination server to create these stored procedures and/or tables.

I tried

SELECT * 
FROM [*DestServer*].[*database*].sys.procedures

I just get the following error:

Invalid object name 'DestServer.database.sys.procedures'

Is it even possible to obtain that list I want?

Solution

  • In the example I'm using, my linked server is called DSSQL.

    SELECT * FROM [DSSQL].[master].[sys].[objects] WHERE TYPE='P';