PARSENAME Function

PARSENAME function is a SQL Server Metadata function which returns the specified part of an object name.

The parts of an object that can be retrieved are the object name, schema name, database name, and server name.

SQL Server uses a four part object naming syntax to represent database objects, so the full qualified name of any object can be represented as Server_name.Database_name.schema_name.Object_name.

So, In case if you want to get a specific part of an object then you can user the PARSENAME function.

Also note that, The PARSENAME function does not indicate whether an object by the specified name exists or not in database.

SYNTAX

PARSENAME (object_name, object_piece )

object_name

Is the name of the object (optionally-qualified) for which to retrieve the specified object part. If all parts of the object name are qualified, this name can have four parts: the server name, the database name, the schema name, and the object name.

object_piece
Is the object part to return. It must be an integer value between 1 to 4,  following are the values that you can provide for parameter object_piece to get the specified part of an object.
1 = Object name
2 = Schema name
3 = Database name
4 = Server name

Lets look at an example of using PARSENAME function in SQL Server.

Following is the full qualified name of table Employee which includes server name, database name, schema name.

'[RservRdl].[AdventureWorks2017].[HumanResources].[Employee]'

Lets use the PARSENAME function to get an object part from this full qualified name of an object.

DECLARE @ObjectName AS VARCHAR(100)

SET @ObjectName =
'[RservRdl].[AdventureWorks2017].[HumanResources].[Employee]'

SELECT PARSENAME(@ObjectName, 4) AS ServerName,
PARSENAME(@ObjectName, 3) AS DatabaseName,
PARSENAME(@ObjectName, 2) AS SchemaName,
PARSENAME(@ObjectName, 1) AS TableName

Lets see how PARSENAME function deal with an optionally-qualified object name.

Following object name includes only Schema and table name as shown below.

[HumanResources].[Employee]

Lets use PARSENAME function to get all object parts.

DECLARE @ObjectName AS VARCHAR(100)

SET @ObjectName =
'[HumanResources].[Employee]'

SELECT PARSENAME(@ObjectName, 4) AS ServerName,
PARSENAME(@ObjectName, 3) AS DatabaseName,
PARSENAME(@ObjectName, 2) AS SchemaName,
PARSENAME(@ObjectName, 1) AS TableName

You can see, it returns schema name, and table name from given string, and null for server name and database name as string does not contain server name and database name.

Limitation of PARSENAME Function

This function function does not indicate whether an object by the specified name exists or not in database.

You can see following statement returns an error of invalid object as table TableName does not exists in database.

SELECT * FROM [HumanResources].[TableName]

Lets see, does PARSENAME Function also returns an error of an invalid object or not.

DECLARE @ObjectName AS VARCHAR(100)

SET @ObjectName =
'[HumanResources].[TableName]'

SELECT
PARSENAME(@ObjectName, 2) AS SchemaName,
PARSENAME(@ObjectName, 1) AS TableName

You can see, although there is no as such table with the name TableName in database yet PARSENAME function returns a part of an object without any error.

Also Read..

SQL Server @@version

SQL Server @@spid

SQL Server @@Language

SQL Server @@servicename

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 2,621 total views,  11 views today