How can I get the name of the database server from within a stored procedure?

Question

I'm making complete connection strings inside my procedure and would like to inject the name of the database server in them. Is there any way that I can detect the name from inside or am I doomed to passing it in?

Answer 1

So for MS SQL Server you could:

SELECT @@SERVERNAME

For MySQL, it's

SELECT variable_value as servername
FROM information_schema.global_variables
WHERE variable_name = 'hostname';

For Oracle, its:

SELECT global_name FROM global_name

Answer 2

Use SERVERPROPERTY:

• SERVERPROPERTY('MachineName'): name of the SQL Server host name, cluster aware
• SERVERPROPERTY('ComputerNamePhysicalNetBIOS'): name of physical machine name. In a cluster, is the name of the current active node. On a standalone instalation, is identical with MachineName
• SERVERPROPERTY('InstanceName'): name of the current SQL Server instance. NULL for default.

One thing I'd recomend against is the dreaded @@SERVERNAME. This property is notorious for getting out of sync with reality after a machine rename. I've seen way too many apps burned by this problem to place any trust on it. The correct rename procedure is in BOL, but few use it properly: How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server.