-- create table
USE [ADMINDB]
GO
/****** Object:  Table [dbo].[DiskSpaceStatus]    Script Date: 01/04/2010 13:04:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DiskSpaceStatus](
	[RID] [int] IDENTITY(1,1) NOT NULL,
	[CreatedDate] [datetime] NULL CONSTRAINT [DF_DiskSpaceStatus_CreatedDate]  DEFAULT (getdate()),
	[Drive] [varchar](2) NULL,
	[DriveSpaceFree_MB] [decimal](15, 6) NULL,
	[DatabaseName] [varchar](30) NULL,
	[FileName] [varchar](30) NULL,
	[FileType] [varchar](4) NULL,
	[FileSize] [decimal](15, 2) NULL,
	[SpaceFree_MB] [decimal](10, 2) NULL,
	[PhysicalName] [varchar](128) NULL,
	[PercentFree] [decimal](18, 2) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

-- ================================================
-- exec DiskSpaceReview
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE PROCEDURE DiskSpaceReview

with encryption AS
BEGIN
SET NOCOUNT ON;
---- query unused space all DBs

CREATE TABLE #TMPFIXEDDRIVES ( 
  DRIVE  CHAR(1), 
  MBFREE INT) 

INSERT INTO #TMPFIXEDDRIVES EXEC xp_FIXEDDRIVES 

CREATE TABLE #TMPSPACEUSED ( 
  DBNAME    NVARCHAR(255), 
  FILENME   VARCHAR(255), 
  SPACEUSED FLOAT) 


CREATE TABLE #DB ( 
  NAME    NVARCHAR(255)
  )

DECLARE @dbName sysname, @rc int
DECLARE @cmdSQL varchar(2000)
INSERT INTO #DB (NAME) SELECT NAME FROM master.sys.databases
WHERE 
    --name NOT IN ('master', 'model', 'msdb', 'tempdb', 'ADMINDB', 'ReplDistribution') AND
    --name NOT LIKE '%ReportServer%' AND
    DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
    DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'

SELECT @rc = 1, @dbName = MIN(name)
FROM #DB

WHILE @rc <> 0
BEGIN

SET @cmdSQL = 'USE [' + @dbName + '];' + 'INSERT INTO #TMPSPACEUSED (DBNAME, FILENME, SPACEUSED) SELECT ''' + @dbName + ''', NAME, FILEPROPERTY(NAME, ''SpaceUsed'') FROM [' + @dbName + '].sys.sysfiles'
EXEC(@cmdSQL)

    SELECT TOP 1 @dbName = name
    FROM #db
    WHERE name > @dbName
    ORDER BY name

    SET @rc = @@ROWCOUNT

END
DROP TABLE #DB
INSERT INTO [ADMINDB].[dbo].[DiskSpaceStatus]
           ([Drive]
           ,[DriveSpaceFree_MB]
           ,[DatabaseName]
           ,[FileName]
           ,[FileType]
           ,[FileSize]
           ,[SpaceFree_MB]
           ,[PhysicalName]
           ,[PercentFree])
SELECT   C.DRIVE, 
         CAST((C.MBFREE) AS DECIMAL(15,6)) AS DISKSPACEFREE, 
         A.NAME AS DATABASENAME, 
         B.NAME AS FILENAME, 
         CASE B.TYPE  
           WHEN 0 THEN 'DATA' 
           ELSE TYPE_DESC 
         END AS FILETYPE, 
         --CASE  
           --WHEN (B.SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB' 
         CAST(
         (B.SIZE * 8 / 1024.0) AS DECIMAL(15,2))
         AS FILESIZE_MB, 
         CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(10,2)) AS SPACEFREE_MB, 
         B.PHYSICAL_NAME,
         CAST( ((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0))/(B.SIZE * 8 / 1024.0) AS DECIMAL(18,2))
         AS PercentFree
FROM     SYS.DATABASES A 
         JOIN SYS.MASTER_FILES B 
           ON A.DATABASE_ID = B.DATABASE_ID 
         JOIN #TMPFIXEDDRIVES C 
           ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE 
         JOIN #TMPSPACEUSED D 
           ON A.NAME = D.DBNAME 
              AND B.NAME = D.FILENME 
ORDER BY SPACEFREE_MB DESC
DROP TABLE #TMPFIXEDDRIVES 
DROP TABLE #TMPSPACEUSED 

END