SQL Server – DBCC Info

Background

A few posts back we spoke about “SQL Server – Mapping DBCC Commands to Dynamic Management Views ( DMV )“.

That post is here.

 

DBCC DBINFO

Introduction

DBCC DBINFO offers concise metadata at the database level.

 

SYNTAX

Its syntax is elementary and here it is “DBCC dbinfo with tableresults“.

 

Sample

ROWS

Display result as rows

Query


dbcc dbinfo with tableresults, no_infomsgs

Output

 

COLUMNS

Query



set nocount on
go

set XACT_ABORT on
go


declare @tblDBInfo TABLE
(
      [parentObject] sysname not null

    , [object]		 sysname not null

    , [field]		 sysname not null

    , [sequenceNbr]  smallint 
                     identity(1,1) not null

    , [value]        nvarchar(4000) not null

    , primary key
        (
              [parentObject] 
            , [object]		 
            , [field]		 
            , [sequenceNbr]
        )
)

declare @servername sysname
declare @dbid		int
declare @dbname		sysname

set @servername = cast(
                        serverproperty('servername')
                        as sysname
                      )

set @dbid = db_id()

set @dbname = db_name()

insert into @tblDBInfo
(
      [parentObject]
    , [object]		
    , [field]		
    , [value]       
)
exec ('dbcc dbinfo with tableresults, no_infomsgs')

select *

from   @tblDBInfo

order by 
        [field] asc

; with cteExclusion
(
      [dbid]
    , [Field] 
)
as
(
    select 
           2 as [dbid]
        , 'dbi_dbccLastKnownGood' as [Field]

)
, cteDBInfo
(
      [servername]
    , [dbid]
    , [Field]
    , [VALUE]
)
as
(

    select 
              @servername
            , @dbid
            , tblDBI.Field
            , tblDBI.[VALUE]

    from   @tblDBInfo tblDBI

    where  tblDBI.Field in
            (
                  'dbi_dbccLastKnownGood'
                , 'dbi_version'
                , 'dbi_createVersion'
                , 'dbi_LastLogBackupTime'
                , 'dbi_crdate'
                , 'dbi_dbname'
                , 'dbi_dbid'
                , 'dbi_cmptlevel'
            )


    and not exists
        (
            select 1
            from   cteExclusion cteEX
            where  cteEX.[dbid] = @dbid
            and    cteEX.Field = tblDBI.[Field]   
        )


)

, ctePivot
as
(
    select 
              cteDBI.[serverName]
            , cteDBI.[dbid]
            , cteDBI.[dbi_dbccLastKnownGood]
            , [dbi_version]
            , [dbi_createVersion]
            , [dbi_LastLogBackupTime]
            , [dbi_crdate]
            , [dbi_dbname]
            , [dbi_dbid]
            , [dbi_cmptlevel]	

    from   cteDBInfo cteDBI

    PIVOT
        (
            MAX(VALUE)
    
            FOR [Field] in
            (
                  [dbi_dbccLastKnownGood]
                , [dbi_version]
                , [dbi_createVersion]
                , [dbi_LastLogBackupTime]
                , [dbi_crdate]
                , [dbi_dbname]
                , [dbi_dbid]
                , [dbi_cmptlevel]
            )

        ) cteDBI

)

select 
          cteDBI.[servername]
        , cteDBI.[dbid]
        , cteDBI.[dbi_dbccLastKnownGood]
        , [dbi_version]
        , [dbi_createVersion]
        , [dbi_LastLogBackupTime]
        , [dbi_crdate]
        , [dbi_dbname]
        , [dbi_dbid]
        , [dbi_cmptlevel]

from   ctePivot cteDBI


Output

Summary

DBCC DBINFO has a wealth of information.

Nuggets include:

  1. Database Attributes
    • Compatibility level
    • Collation
    • Containment
    • Create Date
    • Database ID ( dbid )
    • Database name ( dbname )
    • MirrorId ( Mirrored database )
    • familyGUID ( Restored DB & Snapshot )
    • Safety ( Mirrored database )
    • Database Version
    • Database Create Version
  2. Operation Tracking
    • Last time dbcc checkdb was ran
    • Last time Transaction Log backup was taken

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s