Saturday, November 24, 2012

Get Server name, Database name, Schema name or Object name

There is a simple T-SQL function that Returns the specified part of an object name. using this function we can retrieve object name, owner name, database name, and server name from a fully qualified object name. This function doesn't require the object name that is provided is in the database, its only returns the specified part of the specified object name.

PARSENAME ( 'object_name' , object_piece )

object_piece

Object_piece is the part that you want to retrieve.
1 = Object name 
2 = Schema name
3 = Database name
4 = Server name


what this function actually doing is, it getting the object name and split the object name by period (‘.’) and returning the specified position in Object_piece.

SELECT PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',1) AS ObjectName
      ,PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',2) AS SchemaName
      ,PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',3) AS DatabaseName
      ,PARSENAME('ServerName.DatabaseName.SchemaName.ObjectName',4) AS ServerName


if you smart enough you can use this for many things, such as separating extension from the file name or parse an IP address. Just remember you are working backwards.

No comments:

Post a Comment