Read Environment Variables in T-SQL

I had recently posted on Use xp_cmdshell results in T_SQL. One of the blog readers Andrew got back asking if it was possible to read Environment Variables using this method and store the value in a variable for later use.

Here’s the query. The variable @windir holds the value and you can use it as you want: (Thanks to my colleague Karthi for simplying the query further)

DECLARE @windir nvarchar(255)
CREATE TABLE #Tmp
(
EnvVar nvarchar(255)
)
INSERT INTO #Tmp exec xp_cmdshell 'echo %windir%'
SET @windir = (SELECT TOP 1 EnvVar from #Tmp)

SELECT @windir as 'Windows Directory'
-- DROP TABLE #Tmp 

Note: Observe I am using ‘TOP 1’ in the query since the subquery returned more than one row, one of them being a blank row. I am not sure why is the blank row returned and till I find it out, I will stick with the ‘TOP 1’ command.

OUTPUT