Ever cameup with a situation wherein you want to create a databaes on the fly and to decide dynamically where to place your data and log files, yes I faced it and cameup with the following solution.
You have one stored procedure and one function that can help, first xp_fixeddrives and second one is a system function fn_servershareddrives. xp_fixeddrives procedure outputs the list of drives with free space available on each of the drives, but does not tell you which is shared(SAN drive) and which is local. This is important because you cannot create a database on a local drive when SQL Server is running in clustered environment. And function fn_servershareddrives() does not list the free space available and also does not report local drives so combine the results of both and list every thing and pick what you want and create the database, based on isClusterd() server property output. Here is what I did.
SET NOCOUNT ON
CREATE TABLE #DriveSpace (
Drive char(1),
MBFree int
)
-- get availabe drives and free space on each of them
INSERT INTO #DriveSpace EXEC xp_fixeddrives
SELECT * INTO #DriveUnion FROM
(
SELECT A.DriveName COLLATE Latin1_General_CI_AS
[Drives Available], #DriveSpace.MBFree, 'X'[Shared]
FROM ::fn_servershareddrives() A
LEFT JOIN #DriveSpace on
A.DriveName COLLATE Latin1_General_CI_AS = #DriveSpace.drive
UNION
SELECT Drive COLLATE Latin1_General_CI_AS [Drives Available],
MBFree, '-'[Shared]
FROM #DriveSpace
WHERE Drive NOT IN (SELECT DriveName COLLATE Latin1_General_CI_AS
FROM ::fn_servershareddrives())
) A
/**
-- if clustered environment eliminate local drives from the list.
IF SERVERPROPERTY('isClustered')=1
DELETE FROM #DriveUnion where Shared='-'
SELECT * FROM #DriveUnion
**/
DROP TABLE #DriveSpace
DROP TABLE #DriveUnion
Sample Output
'X' indicates shared drive, where as '-' indicates local drive.
Drives Available MBFree Shared
---------------- ----------- ------
C 50469 -
D 36053 -
E 267942 X
F 39384 X
G 3657 X
H 179584 X
