http://www.sqlservercentral.com/blogs/brian_kelley/2005/12/09/simple-script-to-show-what-drives-all-databases-are-on/

Printed 2014/09/17 07:35AM

Simple script to show what drives all databases are on

2005/12/09

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





Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.