Sql Server : Read environment variables from T-SQL
- Posted by Sqltimes
- On June 13, 2015
- 0 Comments
Quick one today:
Sometimes there is a need to query Environment Variables using T-SQL. ‘xp_cmdshell’ allows us to query environment variables. First we need to enable ‘xp_cmdshell’ in ‘sys.configurations’ using EXEC sp_configure. It is an advanced option, so enable ‘advanced options’ first.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
---- Set Configurations--SELECT * FROM sys.configurationsWHERE name = 'xp_cmdshell'GOEXEC sp_configure 'SHOW ADVANCED OPTIONS', 1RECONFIGUREGOEXEC sp_configure 'xp_cmdshell', 1RECONFIGUREGOEXEC sp_configure 'SHOW ADVANCED OPTIONS', 0RECONFIGUREGOSELECT * FROM sys.configurationsWHERE name = 'xp_cmdshell'GO |
Then use xp_cmdshell to query each environment variable, as needed. Or you could create a temporary table and INSERT the result into it.
|
1
2
3
4
5
6
7
|
---- Query environment variables--exec xp_cmdshell 'echo %NUMBER_OF_PROCESSORS%'exec xp_cmdshell 'echo %ProgramFiles%'exec xp_cmdshell 'echo %LTRXDATABASE%'GO |
Result:
Environment Variables Output
Reset the sys.configurations settings after you are done.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
---- Reset Configurations--SELECT * FROM sys.configurationsWHERE name = 'xp_cmdshell'GOEXEC sp_configure 'SHOW ADVANCED OPTIONS', 1RECONFIGUREGOEXEC sp_configure 'xp_cmdshell', 0RECONFIGUREGOEXEC sp_configure 'SHOW ADVANCED OPTIONS', 0RECONFIGUREGOSELECT * FROM sys.configurationsWHERE name = 'xp_cmdshell'GO |
Hope this helps,
_Sqltimes
0 Comments