SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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
Code

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

fully qualified name for SQL Server database tables

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
Code

And above SQL function can be called within SQL codes as follows

select object_id, name, dbo.getFullyQualifiedName(object_id) from sys.tables
Code

fully qualified name for SQL tables using function



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.