Read Environment Variables in SQL Server

-- =======================================================================
-- Read Environment Variables in SQL Server
--
-- Author: Sql Times - https://sqltimes.wordpress.com/author/sqltimes/
-- Article: https://sqltimes.wordpress.com/2015/06/13/sql-server-read-environment-variables-from-t-sql/
-- Date: June 13, 2015
-- =======================================================================

--
-- Set Configurations
--
-- Enable 'xp_cmdshell' in 'sys.configurations' using EXEC sp_configure. It is
-- an advanced option, so enable 'advanced options' first.

select * from sys.configurations where name = 'xp_cmdshell';
go

exec sp_configure
    @configname  = 'SHOW ADVANCED OPTIONS',
    @configvalue = 1;
reconfigure;
go

exec sp_configure
    @configname  = 'xp_cmdshell',
    @configvalue = 1;
reconfigure;
go

exec sp_configure
    @configname  = 'SHOW ADVANCED OPTIONS',
    @configvalue = 0;
reconfigure;
go

select * from sys.configurations where name = 'xp_cmdshell';
go

--
-- Query environment variables
--
-- Then use xp_cmdshell to query each environment variable, as needed. Or you could
-- create a temporary table and INSERT the result into it.

exec xp_cmdshell 'echo %NUMBER_OF_PROCESSORS%';
exec xp_cmdshell 'echo %ProgramFiles%';
exec xp_cmdshell 'echo %LTRXDATABASE%';
go

--
-- Reset Configurations
--
-- Reset the sys.configurations settings after you are done.

select * from sys.configurations where name = 'xp_cmdshell';
go

exec sp_configure
    @configname  = 'SHOW ADVANCED OPTIONS',
    @configvalue = 1;
reconfigure;
go

exec sp_configure
    @configname  = 'xp_cmdshell',
    @configvalue = 0;
reconfigure;
go

exec sp_configure
    @configname  = 'SHOW ADVANCED OPTIONS',
    @configvalue = 0;
reconfigure;
go

select * from sys.configurations where name = 'xp_cmdshell';
go