Presents your 
SQL SERVER E-NEWSLETTER for June 27, 2002

--------------------------------------------

Capture important database information

Do you need a quick report on an instance of SQL Server to report
information on each database? Below is a script that uses a cursor to traverse
the databases and executes the sp_helpdb command. The output shows the following:


* The number of database files

* The size of the files

* The filegroups to which the files belong

* The current size of files

* The maximum size of files

* The order of the files

* File growth information


If you're not already reporting this information, it's imperative to
capture it in order to rebuild the database from scratch in case of disaster recovery.

USE master
GO
DECLARE Csr CURSOR FOR
SELECT name
FROM    sysdatabases
ORDER BY NAME

DECLARE
    @name VARCHAR(32), @cmd VARCHAR(255)

SET NOCOUNT ON
OPEN Csr
FETCH NEXT FROM Csr INTO @name
WHILE (@@fetch_status <> -1)
BEGIN

    IF (@@fetch_status <> -2)

    BEGIN
     SELECT @cmd = 'USE ' + @name  + '
     PRINT REPLICATE(''____' +DB_NAME() + '__'',4) EXEC sp_helpfile'
     EXEC (@cmd)
    END
    FETCH NEXT FROM Csr INTO @name

END
CLOSE Csr
DEALLOCATE Csr

----------------------------------------