Simple Query to Derive Machine Name from SQL Instance Name

If you have a string containing the name of a SQL server, here’s a simple script to derive the Windows computer name from the instance name using SUBSTRING:

DECLARE @SQLInstance NVARCHAR(128)
DECLARE @SQLMachineName NVARCHAR(128)

SET @SQLInstance = @@SERVERNAME

-- Derive @SQLMachineName from @SQLInstance
IF CHARINDEX('', @SQLInstance) = 0
BEGIN
	SELECT @SQLMachineName = @SQLInstance
END
ELSE
BEGIN
	SELECT @SQLMachineName = SUBSTRING(@SQLInstance, 1, CHARINDEX('', @SQLInstance)-1)
END

References:

SUBSTRING (Transact-SQL)

Leave a Reply

Your email address will not be published. Required fields are marked *