Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

Simple script to show what drives all databases are on

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




Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.