Skip to content

SqlSkull

SQL Server | SSRS | SSIS | Power BI | DAX
  • Home
  • About Us
  • Microsoft SQL Server
    • SQL Basics Concepts
    • SQL Advance Concepts
    • SQL Interview Q & A
    • SQL Server Tricks & Tips
  • SSRS
  • SSIS
  • POWER BI
  • Python with ML
  • Microsoft Azure
  • HTML Color picker

SqlSkull

SQL Server | SSRS | SSIS | Power BI | DAX
  • Home
  • About Us
  • Microsoft SQL Server
    • SQL Basics Concepts
    • SQL Advance Concepts
    • SQL Interview Q & A
    • SQL Server Tricks & Tips
  • SSRS
  • SSIS
  • POWER BI
  • Python with ML
  • Microsoft Azure
  • HTML Color picker
Home ยป PARSENAME Function

PARSENAME Function

  • by Pradeep Raturi
  • SQL Advance, SQL SERVER, SQL Tricks & Tips

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 TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 2,621 total views,  11 views today

Share this:

  • Click to share on Facebook (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on WhatsApp (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Related

Tags:Example Uses of the PARSENAME Function - SQL ServerMetadata function in SQLPARSENAME function in SQLPARSENAME Metadata function in SQLSQLSQL - PARSENAMESQL DatabaseSQL PARASENAMESQL Server Interview Question and AnswerSQL Server Metadata FunctionsSQL SERVER PARSENAME Functionsql server tipsSQL Server TutorialSQL Server TutorialsSQL Server Tutorials By Pradeep Raturisql tipsSQL TutorialSQL Tutorialst-sqlUse PARSENAME() to Return Part of an Object Name in SQLWhat is Parsename SQL Server?

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

previousImport text using examples feature Power BI
nextView Collation Information in SQL Server
Generic selectors
Exact matches only
Search in title
Search in content

SQL Advance

  • SQL Advance
    • SQL Server Stored Procedure
    • How to Encrypt a Stored Procedure in SQL Server
    • Insert the output of a stored procedure into table/temp table
    • SQL Server Stored Procedures Vs User Defined Functions
    • SQL SERVER VIEW
    • SQL Server OUTPUT CLAUSE
    • SQL MERGE
    • SQL Server PIVOT
    • SQL Server Dynamic PIVOT
    • SQL Server UNPIVOT
    • SQL SERVER ROLLUP
    • SQL Server Synonyms
    • SQL Server CUBE
    • SQL Server GROUPING SETS
    • SQL SERVER User Defined Table Type and Table Valued Parameters
    • USER DEFINED FUNCTIONS
    • SQL Server Create SCHEMA
    • SQL Server Sequence
    • SQL Server OFFSET FETCH
    • SQL Server CTE
    • SQL Server Recursive CTE
    • SQL Server Change Data Capture
    • SQL Server TEMPORAL TABLE
    • SQL Server Cursor
    • SQL WAITFOR
    • IDENT_CURRENT Function
    • DROP IF EXISTS
    • SQL ERROR HANDLING WITH TRY..CATCH
    • SQL SERVER IF..ELSE
    • SQL GOTO Statement
    • SQL Server WHILE
    • SQL Return
    • SQL JSON_VALUE Function
    • SQL Server FOR JSON Clause
    • SQL JSON_QUERY Function
    • SQL JSON_MODIFY Function
    • SQL Server FOR XML Clause
    • SQL SERVER SET XACT_ABORT
    • SET STATISTICS IO
    • SQL SET Language
    • SQL SET ROWCOUNT
    • SQL SET NOCOUNT ON
    • SQL SERVER SET IDENTITY_INSERT
    • SQL SERVER SET DATEFORMAT
    • SQL CLUSTERED INDEX
    • Difference between clustered index and non clustered index
    • SQL Triggers
      • SQL DML AFTER TRIGGER
      • SQL DML Instead Of Trigger
      • SQL LOGON Trigger
      • SQL DDL Trigger
    • Get the definitions of Stored procedures, User Defined functions and Triggers in SQL
    • SQL ROLLBACK Transactions
    • SQL Server Dirty Reads in concurrent transactions
    • Lost update problem in concurrent transations
    • Identify Blocked Processes or Long Running Query In SQL Server
    • Schedule a Job in SQL
    • SQL sp_helptext
    • SQL Server sp_procoption startup procedures
    • SQL Server sp_executesql
    • SQL SERVER SP_MONITOR
    • SP_RENAME in SQL Server
    • SQL sp_help
    • How to Read SQL Server error logs using the xp_readerrorlog
    • SQL Server Database Mail Configurations
    • Send a SQL Server query results in e-mail message
    • Troubleshooting Database Mail in SQL Server
    • Import file in SQL server
    • Export data from SQL Server to an Excel file
    • Bulk Insert in SQL Server
    • Copy or delete files from directory in SQL Server
    • Row Level Security in SQL Server
    • Dynamic Data Masking in SQL Server
    • SQL Vulnerability Assessment
    • Column Level Encryption Decryption using Symmetric Keys
    • SQL Data Discovery and Classification
    • Script table in SQL Server
    • Generate Database Scripts With Data in SQL Server
  • SQL Basics Concepts
  • SQL Interview Q & A
  • SQL Server Tricks & Tips
  • SSRS-SQL Server Reporting Services

Page Views

  • 644,712 hits

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Translate this website..

Search for Tutorial

Generic selectors
Exact matches only
Search in title
Search in content
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Home
  • SQL Basics Concepts
  • SQL Advance Concepts
  • SQL Interview Q & A
  • SQL Server Tricks & Tips
  • SSRS-SQL Server Reporting Services
  • SSIS-SQL Server Integration Services
  • POWER BI
  • Python with Machine Learning
  • Microsoft Azure
  • HTML Color picker

SqlSkull. All Rights Reserved

 

Loading Comments...