0

Why am I unable to see Stored Procedures or functions when I open a Linked Server in SSMS? I have created the Linked Server and can see the databases, but within each database, only tables and views are shown. The Linked Server is connected to another SQL Server instance installed on another Server, I expected to see too Programmability folder and within it the Stored procedures and functions enter image description here

1 Answer 1

0

This is a limitation in SSMS as what I'm sure was a design choice by the developers who created it.

The reasoning likely being is because a Linked Server object can be created for a multitude of different database systems, e.g. Oracle SQL, MySQL, Snowflake database, etc. Not every database system has Procedures and Functions, or if they do, not implemented in the same way SQL Server does. Whereas Tables and Views are pretty consistent concepts and object types that exist in most modern database systems.

So to create a generic solution in SSMS, Linked Servers only expose Tables and Views under the Linked Server object node. But those other objects are still queryable (if they exist) when you write T-SQL that references them with the 4-part name. E.g: EXEC MyLinkedServer.DatabaseName.SchemaName.spSomeStoredProcedure is valid syntax.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.