One would think that this would be easy.

I am trying to gather information on database file sizes, and spaced
used inside the file. For the most part I have be successful in
gathering the information. However I have run into a problem.

I still need to fix some errors such as if the database is offline,
some how report that. And fix the loop for DBCC SHOWFILESTATS to show
multiple files attached as Data. And that brings me to my next
question. Is there a way to list the percentage used of space used for
all log files within a sing database. Currently all the commands seem
to list only the total, not the individual files.

I am currently constrained by SQL 2000

[code]

DECLARE @dbname varchar(50)
DECLARE @string varchar(250)
SET @string = ''

CREATE TABLE #dbcc_showfilestats (
    fileid               tinyint,
    FileGroup1         tinyint,
    TotalExtents1      decimal (28, 2),
    UsedExtents1       decimal (28, 2),
    Name               varchar(50),
    FileName           sysname )

CREATE TABLE #dbstats (
    DB_Name             varchar(50),
    DB_Total_Size_in_MB decimal (28, 2),
    DB_Used_Size_in_MB  decimal (28, 2),
    DB_Free_Size_in_MB  decimal (28, 2),
    DB_Percent_Used     decimal (28, 2),
    File_Type            tinyint,
    DB_ID                int)

DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
-- Collects all the DB name
OPEN dbnames_cursor

FETCH NEXT FROM dbnames_cursor INTO @dbname

WHILE (@@fetch_status = 0)
BEGIN
    SET @string = 'use ' + @dbname + ' DBCC SHOWFILESTATS'
    INSERT #dbcc_showfilestats
    EXEC (@string)

-- SELECT * FROM #dbcc_showfilestats  -- Debug

    INSERT #dbstats (DB_Name, DB_Total_Size_in_MB, DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
    SELECT @dbname,
           DB_Total_Size_in_MB = sum(TotalExtents1)*65536.0/1048576.0,
           DB_Used_Size_in_MB  = sum(UsedExtents1)*65536.0/1048576.0,
           DB_Free_Size_in_MB  =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
           DB_Percent_Used     = sum(UsedExtents1/TotalExtents1)*100
    FROM #dbcc_showfilestats
    UPDATE #dbstats SET File_Type = 1,
    DB_ID = (select dbid from master..sysdatabases where name = DB_Name)
    TRUNCATE TABLE #dbcc_showfilestats

FETCH NEXT FROM dbnames_cursor INTO @dbname
END

CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor

SELECT * FROM #dbstats --Debug

/**************************************************
Script to calculate information about the Log Files
**************************************************/

CREATE TABLE #dbcc_sqlperf (
    Log_Name             varchar(50),
    Log_Size             decimal (28, 5),
    Log_Used_Percent     decimal (28, 5),
    Status               tinyint )

CREATE TABLE #logstats (
    Log_Name             varchar(50),
    Log_Total_Size_in_MB decimal (28, 2),
    Log_Used_Size_in_MB  decimal (28, 2),
    Log_Free_Size_in_MB  decimal (28, 2),
    Log_Percent_Used     decimal (28, 2),
    File_Type            tinyint,
    DB_ID                int)

INSERT #dbcc_sqlperf EXEC ('dbcc sqlperf(logspace) with no_infomsgs')
-- SELECT * FROM #dbcc_sqlperf --Debug

    INSERT #logstats (Log_Name, Log_Total_Size_in_MB,
Log_Used_Size_in_MB, Log_Free_Size_in_MB, Log_Percent_Used)
    SELECT Log_Name = Log_Name,
           Log_Total_Size_in_MB = log_size,
           Log_Used_Size_in_MB  = sum
(log_size*(log_used_percent/100)),
           Log_Free_Size_in_MB  = sum (log_size
-(log_size*(log_used_percent/100))),
           Log_Percent_Used     = log_used_percent
    FROM #dbcc_sqlperf
    GROUP BY Log_Name, Log_Size, Log_Used_Percent, Status
    UPDATE #logstats SET File_Type = 0,
    DB_ID = (select dbid from master..sysdatabases where name = Log_Name)

SELECT * FROM #logstats --Debug

/**************************************************
File information.
**************************************************/

CREATE TABLE #master_sysaltfiles (
    File_ID                 tinyint, -- 2 = logs, 1 = data
    DB_ID                    tinyint,
    File_Logical_Name       varchar(100),
    File_Path               varchar(1000),
    File_Type                tinyint,
    File_Size_in_MB         decimal(28,2),
    File_Max_Size_in_MB     decimal(28,2), -- Maximum file size, in 8-KB
pages.
    File_Growth_in_MB       decimal(28,2), -- 0 = No growth. Can be either
the number of pages or the percentage of file size, depending on the
value of status. If status is 0x100000, growth is the percentage of
file size; otherwise, it is the number of pages.
    File_Growth_Percent     decimal(28,2))

INSERT #master_sysaltfiles (File_ID, DB_ID, File_Logical_Name,
File_Path, File_Type, File_Size_in_MB, File_Max_Size_in_MB,
File_Growth_in_MB, File_Growth_Percent)
SELECT
    File_ID               = fileid,
    DB_ID                 = dbid,
    File_Logical_Name     = name,
    File_Path             = filename,
    File_Type             = groupid,
    File_Size_in_MB       = convert(decimal(28,2),size)*8192/1048576,
    File_Max_Size_in_MB   = convert(decimal(28,2),maxsize)*8192/1048576,
    File_Growth_in_MB     = CASE WHEN status <= 0x100000
                            THEN convert(decimal(28,2),growth)*8192/1048576
                            ELSE convert(decimal(28,2),growth)*size*8192/1048576/100 END,
    File_Growth_Percent   = CASE WHEN status <= 0x100000
                            THEN convert(decimal(28,2),growth)/size*100
                               ELSE convert(decimal(28,2),growth) END
FROM master..sysaltfiles
Group By dbid, fileid, filename, name, groupid, size, maxsize, status,
growth

-- SELECT * FROM sys.master_files -- SQL 2005 Only
-- SELECT * FROM master..sysaltfiles
SELECT * FROM #master_sysaltfiles --Debug
-- DROP TABLE #sys_master_files


/***********************************************************
Returns a the STATUS of all databases on a server in English
***********************************************************/
CREATE TABLE #master_sysdatabases (
    Server_Name             varchar(100),
    DB_ID                    int,
    DB_Name                    varchar(100),
    Compatability_Level        int,
    Status_1                varchar(1000),
    Status_2                varchar(1000))

INSERT #master_sysdatabases (Server_Name, DB_ID, DB_Name,
Compatability_Level, Status_1, Status_2)
SELECT @@SERVERNAME AS SERVER,
    DB_ID =    DBID,
    DB_Name = name,
    Compatability_Level = CMPTLEVEL,
    Status_1 = SUBSTRING(
        CASE status & 1 WHEN 0 THEN '' ELSE ', Autoclose' END +
        CASE status & 4 WHEN 0 THEN '' ELSE ', Select Into / Bulk Copy' END +
        CASE status & 8 WHEN 0 THEN '' ELSE ', Truncate Log on Checkpoint'
END +
        CASE status & 16 WHEN 0 THEN '' ELSE ', Torn Page Detection' END +
        CASE status & 32 WHEN 0 THEN '' ELSE ', Loading' END +
        CASE status & 64 WHEN 0 THEN '' ELSE ', Pre-Recovery' END +
        CASE status & 128 WHEN 0 THEN '' ELSE ', Recovering' END +
        CASE status & 256 WHEN 0 THEN '' ELSE ', Not Recovered' END +
        CASE status & 512 WHEN 0 THEN '' ELSE ', Offline' END +
        CASE status & 1024 WHEN 0 THEN '' ELSE ', Read Only' END +
        CASE status & 2048 WHEN 0 THEN '' ELSE ', dbo USE Only' END +
        CASE status & 4096 WHEN 0 THEN '' ELSE ', Single User' END +
        CASE status & 32768 WHEN 0 THEN '' ELSE ', Emergency Mode' END +
        CASE status & 4194304 WHEN 0 THEN '' ELSE ', Autoshrink' END +
        CASE status & 1073741824 WHEN 0 THEN '' ELSE ', Cleanly Shutdown'
END,
        2,8000),
    Status_2 = SUBSTRING(
        CASE status2 & 16384 WHEN 0 THEN '' ELSE ', ANSI NULL Default' END +
        CASE status2 & 65536 WHEN 0 THEN '' ELSE ', Concatenate NULL Yields
NULL' END +
        CASE status2 & 131072 WHEN 0 THEN '' ELSE ', Recursive Triggers' END
+
        CASE status2 & 1048576 WHEN 0 THEN '' ELSE ', Default to Local
Cursor' END +
        CASE status2 & 8388608 WHEN 0 THEN '' ELSE ', Quoted Identifier' END
+
        CASE status2 & 33554432 WHEN 0 THEN '' ELSE ', Cursor CLOSE on
Commit' END +
        CASE status2 & 67108864 WHEN 0 THEN '' ELSE ', ANSI NULLs' END +
        CASE status2 & 268435456 WHEN 0 THEN '' ELSE ', ANSI warnings' END +
        CASE status2 & 536870912 WHEN 0 THEN '' ELSE ', Full Text Enabled'
END,
        2,8000)
FROM master..sysdatabases
-- select * from master..sysdatabases
Select * From #master_sysdatabases

/**************************************************
Compile Information
**************************************************

SELECT *
FROM #master_sysdatabases INNER JOIN #master_sysaltfiles
    ON #master_sysdatabases.DB_ID = #master_sysaltfiles.DB_ID

**************************************************
Drop Tables
**************************************************/
DROP TABLE #dbstats
DROP TABLE #dbcc_showfilestats
DROP TABLE #logstats
DROP TABLE #dbcc_sqlperf
DROP TABLE #master_sysaltfiles
DROP TABLE #master_sysdatabases

[/code]

Looping vs. Set operation - question
Create table as I need it, how to reference in query?
Help with Stored Procedure, Taking too long to run...
Determining single-column candidate keys
Formatting Data