Ack... I forgot that the Name column in SysDatabases is of the SysName datatype which is actually NVARCHAR... cuts the capabilities of the script in half without a conversion.

Also, 8000 characters isn't much to work with in SQL Server 2000... we have a server instance that has 445 databases with pretty big names on it (not MY idea 😉 ). So, I rewrote the script a bit... it's still not unlimited like a cursor or while loop would be, though... Varchar(MAX) would help a lot in SQL Server 2k5 but even that wouldn't allow for unlimited (although you'd be in a lot worse shape than I if you needed it that big 😛 ).

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

I hate to admit this, but a WHILE loop may be better for scalability in this case (although the code above handled 445 long database names)... especially if you have to mod the code with NVARCHAR to contend with non-US database names.

The side benefit of the code is that it does show a method for making some pretty long dynamic SQL on SQL Server 2k...