Home | About Nakul Vachhrajani | Archives
May 9, 2013 9:00 AM
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 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 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:
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.
Until we meet next time,
Be courteous. Drive responsibly.
Posted by nakulvachhrajani
Categories: #SQLServer, Blog, Imported from BeyondRelational
Tags: #SQLServer
Mobile Site | Full Site
Get a free blog at WordPress.com Theme: WordPress Mobile Edition by Alex King.
