SQL Server 2016 – quotename(object_schema_name(@objectId)) Returns Null When Called as a Synonym

sql serversql-server-2016

I have a central utility database in each SQL Server instance, called Utility. In it, I have a bunch of centralized functions and procedures that I call from other databases.

A function I have in it is called GetFqn:

create function [dbo].[GetFqn](@objectId bigint)
returns varchar(100)
as
begin
    declare @fqn varchar(100);
    select @fqn = quotename(object_schema_name(@objectId)) + '.' + quotename(object_name(@objectId));
    return @fqn;
end

When I call this function from another database it returns NULL:

select Utility.dbo.GetFqn([object_id])
from sys.tables

What should I do to make it work?

Best Answer

You have to add a parameter which indicates a database:

create function [dbo].[GetFqn](@objectId bigint, @DatabaseId INT)
returns varchar(100)
as
begin
    declare @fqn varchar(100);
    select @fqn = quotename(object_schema_name(@objectId, @DatabaseId)) + '.' + quotename(object_name(@objectId, @DatabaseId));
    return @fqn;
end

Then call:

select Utility.dbo.GetFqn([object_id], DB_ID())
from sys.tables
Related Question