In my previous post, we looked at the various naming conventions available to us when working with database objects.

Often we may face the need to either generate three-part object names for all objects in the database, or parse a three part name into it’s constituent parts. This post provides quick scripts to achieve this.

Generating Multi-Part Names

Generating multi-part names is a simple affair of querying the sys.objects table as shown in the script below:

USE AdventureWorks2012;
GO

SELECT CAST(SERVERPROPERTY('ServerName') AS VARCHAR(100)) + '.' + 
           DB_NAME() + '.' + 
           SCHEMA_NAME(so.schema_id) + '.' + 
           so.name AS FourPartName,
       DB_NAME() + '.' + 
           SCHEMA_NAME(so.schema_id) + '.' + 
           so.name AS ThreePartName,
       SCHEMA_NAME(so.schema_id) + '.' + 
           so.name AS TwoPartName,
       so.schema_id AS SchemaId,
       so.name AS ObjectName,
       so.type AS ObjectType,
       so.type_desc AS ObjectTypeDescription
FROM sys.objects AS so
WHERE so.is_ms_shipped = 0 AND so.type IN ('U','P','V','TR')
ORDER BY so.schema_id ASC, so.type DESC;
GO

Parsing Multi-Part Names

Parsing multi-part names does not involve any fancy string manipulation, but can be easily done using a system function – PARSENAME().

The PARSENAME function accepts two parameters – the multi-part object name to be parsed and a number from 1-4 indicating the part that needs to be fetched from the supplied name, as shown below:

• 1 = Object Name
• 2 = Schema Name
• 3 = Database Name
• 4 = Server Name

If a particular name is not found in the string supplied, the PARSENAME() function returns NULL.

The query to parse multi-part names into their constituents is available below:

USE AdventureWorks2012;
GO

DECLARE @objectNames TABLE (MultiPartName VARCHAR(100));

INSERT INTO @objectNames (MultiPartName)
VALUES ('W8SQLSERVERSQL2K12.AdventureWorks2012.HumanResources.Employee'),
       ('AdventureWorks2012.HumanResources.Employee'),
       ('HumanResources.Employee')

SELECT oname.MultiPartName AS SuppliedMultiPartName,
       PARSENAME(oname.MultiPartName,4) AS ServerName,
       PARSENAME(oname.MultiPartName,3) AS DatabaseName,
       PARSENAME(oname.MultiPartName,2) AS SchemaName,
       PARSENAME(oname.MultiPartName,1) AS ObjectName
FROM @objectNames AS oname;
GO

Notice that the PARSENAME() function returned NULL for the ServerName column when interpreting a three-part parameter. Similarly, we get NULL for ServerName and DatabaseName when working with a two-part name.

I trust the scripts above were useful to you.

Further Reading

• PARSENAME() – http://msdn.microsoft.com/en-us/library/ms188006.aspx
• TIP: PARSENAME simply parses the string based on the decimal point (.) – it can therefore be used for string manipulation as well!

Until we meet next time,

Be courteous. Drive responsibly.