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..
3,624 total views, 3 views today