Get Fully Qualified Name of Database Object in SQL Server
To find fully qualified name of a database table in SQL Server, developers can use getFullyQualifiedName SQL function to read FQN formed of schema, database and server name together with table name. Although these database object properties can be easily fetched from sys.tables system view columns, I tried to code all in one user-defined SQL function.
Following SQL Select script displays object properties like object_id, object name, schema name, database name where the object is created and the current server name
Additionally by using SQL CONCAT function, SQL programmers can concatenate basic properties of a database table object which forms the fully qualified name for that table.
select
@@SERVERNAME [server name],
DB_NAME() [database name],
SCHEMA_NAME(schema_id) [schema name],
name [table name],
object_id,
"fully qualified name (FQN)" =
concat(QUOTENAME(DB_NAME()), '.', QUOTENAME(SCHEMA_NAME(schema_id)),'.', QUOTENAME(name))
from sys.tables
where type = 'U' -- USER_TABLE
SQL QUOTENAME function encapsulates the object name and prevents SQL Server syntax error incase the database object includes a space character or a dot (.) in its name.
When I execute above code on my SQL Server sample database "kodyaz.development" I got following output
Of course, Transact-SQL programmers can easily convert this code block into a user-defined SQL function as follows.
create function dbo.getFullyQualifiedName(
@objectid int
) returns sysname
begin
declare @fqn sysname
select
@fqn = CONCAT(
QUOTENAME(@@SERVERNAME), '.',
QUOTENAME(DB_NAME()), '.',
QUOTENAME(SCHEMA_NAME([schema_id])),'.',
QUOTENAME([name])
)
from sys.tables
where object_id = @objectid
return @fqn
end
go
And above SQL function can be called within SQL codes as follows
select object_id, name, dbo.getFullyQualifiedName(object_id) from sys.tables
