Using Environment variables in T-SQL

Question

How can I read the value of a system environment variable in a T-SQL script?

This is to run on SQL Server 2005.

Answer 1

This should give you a list (provided you allow people to execute xp_cmdshell)

exec master..xp_cmdshell 'set'

Note: xp_cmdshell is a security hazard ...

You could also do this with a managed stored proc an extended stored proc or via a com component.

Answer 2

xp_cmdshell is generally best avoided for security reasons.

You're better off using a CLR assembly. Here's a good introduction to creating a CLR assembly.

You can use System.Environment.GetEnvironmentVariable() in C# - you'll find more info on how to do that here.

Answer 3

Thanks for the answers. They helped me get to a working solution, although this is probably not the most advanced method:

declare @val varchar(50)
create table #tbl (h varchar(50))
insert into #tbl exec master..xp_cmdshell 'echo %computername%'
set @val = (select top 1 h from #tbl)
drop table #tbl

Specifically I was trying to get the hostname, the echo %computername% could be replaced with the hostname system command. But this now works for any environment variable.

Answer 4

Hey, if you want to get the server name, just call SELECT @@SERVERNAME

Answer 5

To "read the value of a system environment variable in a T-SQL script" you can set SQL Management Studio to use "sqlcmd Mode".

Then you can use like this:

Print '$(TEMP)'

:r $(Temp)\Member.sql
go

I'm not sure how this is done outside of "SQL Management Studio" but it should be hard to find out.