Finding the I/O utilization of all databases in SQL Server.

use tempdb
go

if exists (select [id] from sysobjects where [id] = OBJECT_ID ('#TBL_DATABASEFILES'))
 DROP TABLE #TBL_DATABASEFILES


if exists (select [id] from sysobjects where [id] = OBJECT_ID ('#TBL_FILESTATISTICS'))
 DROP TABLE #TBL_FILESTATISTICS


DECLARE @INT_LOOPCOUNTER INTEGER
DECLARE @INT_MAXCOUNTER INTEGER
DECLARE @INT_DBID INTEGER
DECLARE @INT_FILEID INTEGER
DECLARE @SNM_DATABASENAME SYSNAME
DECLARE @SNM_FILENAME SYSNAME
DECLARE @NVC_EXECUTESTRING NVARCHAR(4000)

DECLARE @MTB_DATABASES TABLE (
ID INT IDENTITY,
DBID INT,
DBNAME SYSNAME )

CREATE TABLE #TBL_DATABASEFILES (
ID INT IDENTITY,
DBID INT,
FILEID INT,
FILENAME SYSNAME,
FILENAME1 varchar(600),
DATABASENAME SYSNAME)

INSERT INTO @MTB_DATABASES (DBID,DBNAME) SELECT DBID,NAME FROM MASTER.DBO.SYSDATABASES ORDER BY DBID
SET @INT_LOOPCOUNTER = 1
SELECT @INT_MAXCOUNTER=MAX(ID) FROM @MTB_DATABASES
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER
BEGIN
 SELECT @INT_DBID = DBID,@SNM_DATABASENAME=DBNAME FROM @MTB_DATABASES WHERE ID = @INT_LOOPCOUNTER
 SET @NVC_EXECUTESTRING = 'INSERT INTO #TBL_DATABASEFILES(DBID,FILEID,FILENAME, FILENAME1,DATABASENAME) SELECT '+STR(@INT_DBID)+' AS DBID,FILEID,NAME AS FILENAME, FILENAME AS FILENAME1,'''+@SNM_DATABASENAME+''' AS DATABASENAME FROM ['+@SNM_DATABASENAME+'].DBO.SYSFILES'
 EXEC SP_EXECUTESQL @NVC_EXECUTESTRING
 SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1
END
--'OK WE NOW HAVE ALL THE DATABASES AND FILENAMES ETC....

CREATE TABLE #TBL_FILESTATISTICS (
ID INT IDENTITY,
DBID INT,
FILEID INT,
DATABASENAME SYSNAME,
FILENAME SYSNAME,
SAMPLETIME DATETIME,
NUMBERREADS BIGINT,
NUMBERWRITES BIGINT,
BYTESREAD BIGINT,
BYTESWRITTEN BIGINT,
IOSTALLMS BIGINT)

SELECT @INT_MAXCOUNTER=MAX(ID) FROM #TBL_DATABASEFILES
SET @INT_LOOPCOUNTER = 1
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER
BEGIN
 SELECT @INT_DBID = DBID,@INT_FILEID=FILEID,@SNM_DATABASENAME=DATABASENAME,@SNM_FILENAME=FILENAME FROM #TBL_DATABASEFILES WHERE ID = @INT_LOOPCOUNTER
 INSERT INTO #TBL_FILESTATISTICS(DBID,FILEID,SAMPLETIME,NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,DATABASENAME,FILENAME)
 SELECT DBID,FILEID,GETDATE(),NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,@SNM_DATABASENAME AS DATABASENAME,@SNM_FILENAME AS FILENAME FROM :: FN_VIRTUALFILESTATS(@INT_DBID,@INT_FILEID)
 SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1
END
select * from #TBL_FILESTATISTICS

drop table #TBL_DATABASEFILES
drop table #TBL_FILESTATISTICS
go