-- for SQL 2000
 -- this will return just the instance name, so is not really good enough
SELECT SERVERPROPERTY('InstanceName')


-- the following code will return the machine name, but does rely on xp_cmdshell
-- which can be a problem as most systemsleave  it disabled
CREATE TABLE #temp (
                id tinyint IDENTITY(1,1),
                txt VARCHAR(255))
INSERT #temp (txt)
    EXEC MASTER.dbo.xp_cmdshell 'ping localhost -n 1'
SELECT SUBSTRING(txt,9,CHARINDEX('[',txt)-10)
    FROM #temp
    WHERE id=2
DROP TABLE #temp

 SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')