sp_MSget_qualified_name SQL Server Stored Procedure
SQL Server administrators and T-SQL developers can use sp_MSget_qualified_name stored procedure to get the qualified name of a database object.
Here is a sql script showing how to use SQL Server sp_MSget_qualified_name stored procedure.
USE Northwind
GO
DECLARE @object_id INT
DECLARE @qualified_name NVARCHAR(512)
SELECT @object_id = OBJECT_ID('orders')
EXEC sp_MSget_qualified_name @object_id, @qualified_name OUTPUT
SELECT @qualified_name AS QualifiedName
As sql developers will realize the object id of the database object is passed as an input argument to the SQL Server undocumented stored procedure sp_MSget_qualified_name.
And the qualified name of the database object is returned back via OUTPUT argument parameter.
If you miss the OUTPUT hint that is at the end of the "EXEC sp_MSget_qualified_name @object_id, @qualified_name OUTPUT" sql statement, although the syntax is correct and will not throw an error the output qualified name parameter will be NULL.
EXEC sp_MSget_qualified_name @object_id, @qualified_name -- OUTPUT
The above command will retuen NULL, actually since we did not use the OUTPUT hint after the @qualified_name parameter SQL Server sp_MSget_qualified_name stored procedure will not pass the qualified name to the assigned @qualified_name return parameter.
The following wrong syntax of the sp_MSget_qualified_name stored procedure will cause the sql engine to throw an exception:
"EXEC sp_MSget_qualified_name @object_id"
Server: Msg 201, Level 16, State 3, Procedure sp_MSget_qualified_name, Line 0
Procedure 'sp_MSget_qualified_name' expects parameter '@qualified_name', which was not supplied.
This time if you forget to pass the object id parameter as follows :
"EXEC sp_MSget_qualified_name @qualified_name OUTPUT" ,
this misusage will also follow SQL Server to throw the following sql error message.
Server: Msg 8162, Level 16, State 2, Procedure sp_MSget_qualified_name, Line 0
Formal parameter '@object_id' was defined as OUTPUT but the actual parameter not declared OUTPUT.
The sql stored procedure sp_MSget_qualified_name accepts the @object_id parameter in int integer data type.
EXEC sp_MSget_qualified_name 'orders', @qualified_name OUTPUT
Server: Msg 8114, Level 16, State 1, Procedure sp_MSget_qualified_name, Line 0
Error converting data type varchar to int.
Unfortunately, the sp_MSget_qualified_name undocumented stored procedure is no longer supported on Microsoft SQL Server 2008, SQL Server 2008 R2 and SQL Server Denali (SQL 2011) versions.
declare @qualified_name sysname
declare @objectid int = OBJECT_ID('Categories')
EXEC sp_MSget_qualified_name @objectid, @qualified_name OUT
select @qualified_name
If you run the above t-sql script, you will get the following sql error indicating that the sp_MSget_qualified_name does not exist.
Msg 2812, Level 16, State 62, Line 4
Could not find stored procedure 'sp_MSget_qualified_name'.