T-SQL Get Folder Path

[GottaGetITDone]

Hi Guys,

Is there any way using T-SQL to get the 'Program Files' path? E.g Can I get it to resolve something like %PROGRAMFILES% or %WINDIR%?

I ask cause I want to change the statement below so that its not hard coded to c:\.

CREATE DATABASE [Static] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Static.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Static_log.ldf' )
 FOR ATTACH ;
GO

Something like this would be perfect:

CREATE DATABASE [Static] ON 
( FILENAME = N %PROGRAMFILES% + '\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Static.mdf' ),
( FILENAME = N %PROGRAMFILES% + '\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Static_log.ldf' )
 FOR ATTACH ;
GO

Thanks guys,
Matt.

[jp140768]

Here is a very nasty way of doing what you want. I'm sure there are better options out there, but it might get you started:

DECLARE @command varchar(255),@iCount integer

--Create Table Log_CmdOutput (Res VarChar(2000))

set @command = 'Set'

DELETE from  Log_CMDOutput

INSERT INTO Log_CMDOutput values(@Command)

INSERT INTO Log_CMDOutput
EXEC master..xp_cmdshell @Command

Select * From Log_CmdOutPut
Where Res Like 'ProgramFiles%'

HTH