0

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?

2
  • How do you get [object_id]?
    – McNets
    Commented Oct 22, 2019 at 14:49
  • [object_id] is a field of sys.tables Commented Oct 22, 2019 at 16:02

1 Answer 1

2

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
1
  • In fact my hole purpose of creating this function is to reduce the amount of boilerplate code required for simple operations in SQL Server. While db_id() solves the problem, and I'm thankful for your answer, it would be counterproductive. Commented Oct 22, 2019 at 14:23

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.