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?

  • How do you get [object_id]? – McNets Oct 22 at 14:49
  • [object_id] is a field of sys.tables – Saeed Neamati Oct 22 at 16:02
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
  • 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. – Saeed Neamati Oct 22 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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