Had a need for this the other day. We just needed a quick dump of which

drives each database was using. Don't know if it's good enough to make

the useful script category for SSC as I wrote it on the fly (so use at your own risk), but here it is:

DECLARE @database_name sysname

DECLARE @SQL nvarchar(4000)

DECLARE cursDatabases CURSOR FAST_FORWARD

FOR

SELECT name FROM sysdatabases

OPEN cursDatabases

CREATE TABLE #DatabaseDiskUsage

(DatabaseName sysname, DriveLetter char(1))

FETCH FROM cursDatabases INTO @database_name

WHILE (@@FETCH_STATUS = 0)

BEGIN

  SET @SQL = N'INSERT INTO #DatabaseDiskUsage (DatabaseName, DriveLetter)

              

SELECT DISTINCT ''' + @database_name + ''',

              

UPPER(LEFT(filename, 1)) FROM ' + @database_name + '..sysfiles'

  EXEC sp_executesql @SQL

  FETCH NEXT FROM cursDatabases INTO @database_name

END

CLOSE cursDatabases

DEALLOCATE cursDatabases

SELECT DatabaseName, DriveLetter FROM #DatabaseDiskUsage

DROP TABLE #DatabaseDiskUsage