Thanks for the script Jeff.

can I create this script as store proc and run it in reporting service?

I try to do it but it doesn't work

when I put

CREATE PROCEDURE SP_CheckDB

AS

DROP TABLE #T

GO

--===== Supress the auto-display of rowcounts

SET NOCOUNT ON

--===== Create a table to store the results in

CREATE TABLE #T

(

Name SYSNAME,

FileID INT,

FileName NVARCHAR(512),

FileGroup VARCHAR(100),

Size VARCHAR(20),

MaxSize VARCHAR(20),

Growth VARCHAR(20),

Usage VARCHAR(20)

)

--===== Declare a local variable for some dynamic SQL... Could use VARCHAR(MAX) in 2k5

DECLARE @SQL1 VARCHAR(8000)

DECLARE @SQL2 VARCHAR(8000)

DECLARE @SQL3 VARCHAR(8000)

DECLARE @SQL4 VARCHAR(8000)

--===== Create all the commands necessary for ALL databases

SELECT @SQL4 = CASE WHEN LEN(ISNULL(@SQL4,' ')) =7700

THEN ISNULL(@SQL4,'')

+ REPLACE('USE [Mydb] INSERT #T EXEC sp_helpfile','Mydb',CAST(Name AS VARCHAR))

+CHAR(13)

ELSE @SQL4

END,

@SQL3 = CASE WHEN LEN(ISNULL(@SQL3,' ')) =7700

THEN ISNULL(@SQL3,'')

+ REPLACE('USE [Mydb] INSERT #T EXEC sp_helpfile','Mydb',CAST(Name AS VARCHAR))

+CHAR(13)

ELSE @SQL3

END,

@SQL2 = CASE WHEN LEN(ISNULL(@SQL2,' ')) =7700

THEN ISNULL(@SQL2,'')

+ REPLACE('USE [Mydb] INSERT #T EXEC sp_helpfile','Mydb',CAST(Name AS VARCHAR))

+CHAR(13)

ELSE @SQL2

END,

@SQL1 = CASE WHEN LEN(ISNULL(@SQL1,' '))<7700

THEN ISNULL(@SQL1,'')

+ REPLACE('USE [Mydb] INSERT #T EXEC sp_helpfile','Mydb',CAST(Name AS VARCHAR))

+CHAR(13)

ELSE @SQL1

END

FROM Master.dbo.SysDatabases

--===== Execute all the SQL...

EXEC (@SQL1+@SQL2+@SQL3+@SQL4)

--===== Display the results

SELECT * FROM #T ORDER BY Name

any comment on this?

Thanks