Script to Collect Database Information Quickly

Jan 23, 2014 / By Hemantgiri Goswami

Tags: , , , ,

As a DBA there are occasions where we are required to collect details from a database which can 
be used for further analysis. For example, getting the status of the database (i.e. File Size, 
Free Space, Status of the database, and who is the current owner of the database). This kind of 
information is required, and very useful, for auditing purposes in addition to tracking the database/database 
file's size for various reasons. I had a script which does this job for me, exactly the way 
I want it; however, I have to run it for each database separately.  

One fine day, while answering some question's on a forum, I found a script by Dan Guzman which 
retrieved most of the information I needed, and it does this for all the databases. I have 
adopted Dan G.'s script for my use and modified it by adding some more details to it. 

Please review the script below. Let me know if you like it or dislike it. I will try to 
make further improvements on this script.
--==================================================================================================================
-- Script Originally Written By: Dan Guzman | http://www.dbdelta.com/ 
-- Modified by: Hemantgiri S. Goswami 
-- Reference: 
-- http://social.msdn.microsoft.com/Forums/en/transactsql/thread/226bbffc-2cfa-4fa8-8873-48dec6b5f17f
--==================================================================================================================
DECLARE
    @SqlStatement NVARCHAR(MAX)
    ,@DatabaseName SYSNAME;

IF OBJECT_ID(N'tempdb..#DatabaseSpace') IS NOT NULL
    DROP TABLE #DatabaseSpace;

CREATE TABLE #DatabaseSpace
(
    SERVERNAME        SYSNAME,
    DBID            INT,
    DATABASE_NAME    SYSNAME,
    Recovery_Model    VARCHAR(15),
    DBOWNER            VARCHAR(25),
    LOGICAL_NAME    SYSNAME,
    FILE_PATH        SYSNAME,
    FILE_SIZE_MB    DECIMAL(12, 2),
    SPACE_USED_MB    DECIMAL(12, 2),
    FREE_SPACE_MB    DECIMAL(12, 2),
    GROWTH_OPTION    VARCHAR(15),
    MAXIMUM_SIZE    INT,
    AUTOGROWTH        INT,
    DB_STATUS        VARCHAR(100)
);

DECLARE DatabaseList CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases WHERE STATE = 0;

OPEN DatabaseList;
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM DatabaseList INTO @DatabaseName;
    IF @@FETCH_STATUS = -1 BREAK;
    SET @SqlStatement = N'USE '
        + QUOTENAME(@DatabaseName)
        + CHAR(13)+ CHAR(10)
        + N'INSERT INTO #DatabaseSpace
                SELECT
                [ServerName]         = @@ServerName
                ,[DBID]             = SD.DBID
                ,[DATABASE_NAME]    = DB_NAME()
                ,[Recovery_Model]    = d.recovery_model_desc
                ,[DBOwner]             = SUSER_SNAME(sd.sid)
                ,[LOGICAL_NAME]     = f.name
                ,[File_Path]         = sf.filename
                ,[FILE_SIZE_GB]     = (CONVERT(decimal(12,2),round(f.size/128.000,2))/1024)
                ,[SPACE_USED_GB]     = (CONVERT(decimal(12,2),round(fileproperty(f.name,''SpaceUsed'')/128.000,2))/1024)
                ,[FREE_SPACE_GB]     = (CONVERT(decimal(12,2),round((f.size-fileproperty(f.name,''SpaceUsed''))/128.000,2))/1024)
                ,[Growth_Option]     = case sf.status 
                                        & 0x100000
                                        WHEN 1048576    THEN    ''Percentage''                                        
                                        WHEN 0            THEN    ''MB''
                                      END
                ,[Maximum_Size]     = SF.MaxSize
                ,[AutoGrowth(MB)]     = (SF.Growth*8/1024)
                ,[DB_Status]        =
                                    CASE SD.STATUS
                                        WHEN 0 THEN ''Normal''
                                        WHEN 1 THEN ''autoclose'' 
                                        WHEN 2 THEN ''2 not sure'' 
                                        WHEN 4 THEN ''select into/bulkcopy'' 
                                        WHEN 8 THEN ''trunc. log on chkpt'' 
                                        WHEN 16 THEN ''torn page detection'' 
                                        WHEN 20 THEN ''Normal'' 
                                        WHEN 24 THEN ''Normal'' 
                                        WHEN 32 THEN ''loading'' 
                                        WHEN 64 THEN ''pre recovery'' 
                                        WHEN 128 THEN ''recovering'' 
                                        WHEN 256 THEN ''not recovered'' 
                                        WHEN 512 THEN ''offline'' 
                                        WHEN 1024 THEN ''read only'' 
                                        WHEN 2048 THEN ''dbo use only'' 
                                        WHEN 4096 THEN ''single user'' 
                                        WHEN 8192 THEN ''8192 not sure'' 
                                        WHEN 16384 THEN ''16384 not sure'' 
                                        WHEN 32768 THEN ''emergency mode'' 
                                        WHEN 65536 THEN ''online'' 
                                        WHEN 131072 THEN ''131072 not sure'' 
                                        WHEN 262144 THEN ''262144 not sure'' 
                                        WHEN 524288 THEN ''524288 not sure'' 
                                        WHEN 1048576 THEN ''1048576 not sure'' 
                                        WHEN 2097152 THEN ''2097152 not sure'' 
                                        WHEN 4194304 THEN ''autoshrink'' 
                                        WHEN 1073741824 THEN ''cleanly shutdown''
                                    END 
            FROM SYS.DATABASE_FILES F
            JOIN 
            MASTER.DBO.SYSALTFILES SF
            ON F.NAME = SF.NAME
            JOIN 
            MASTER.SYS.SYSDATABASES SD
            ON 
            SD.DBID = SF.DBID
            JOIN
            MASTER.SYS.DATABASES D
            ON 
            D.DATABASE_ID = SD.DBID
            AND DATABASEPROPERTYEX(SD.NAME,''Updateability'') <> ''OFFLINE''
            ORDER BY [File_Size_GB] DESC';
    EXECUTE(@SqlStatement);

END
CLOSE DatabaseList;
DEALLOCATE DatabaseList;

SELECT * FROM #DatabaseSpace;

DROP TABLE #DatabaseSpace;
GO

-- Hemantgiri S. Goswami |Linkedin | Twitter

3 Responses to “Script to Collect Database Information Quickly”

  • Jay Beta says:

    Hi Hemant,

    Nice script. Just one quick comment. The “CREATE TABLE” statement references MB for some of the table names while the “INSERT TABLE” statement references GB. I’m pretty sure you meant GB and based on my results I was able to validate that. Also, you may want to augment the script with one column for the actual percentage of free space left. Good job on the script though!!!

  • hmm , works but that looks more like something from 2000 days with sysaltfiles and database status code.

    sys.master_files can retrieve info about files under databases that are not online because it’s stored under master database so will get full picture of your databases.

    Cheers

  • Samir Saad says:

    I am getting SP2-0310: unable to open file “SqlStatement.sql”
    i.e it does like those @@ signs. what are they for ? and how to get the script to run ?

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>