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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.

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

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.

 @@SERVERNAME [server name],
 DB_NAME() [database name],
 SCHEMA_NAME(schema_id) [schema name],
 name [table name],
 "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

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

 declare @fqn sysname
  @fqn = CONCAT(
 from sys.tables
 where object_id = @objectid
 return @fqn

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

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

fully qualified name for SQL tables using function

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums

Copyright © 2004 - 2020 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems