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, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




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.

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

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

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

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







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