This is the 18th day of my participation in the August Challenge

Display DBCC results in tabular form

Most results of DBCC command execution are in tabular form. Such as DBCC useroptions;

Some are in text format, such as DBCC CHECKDB

The DBCC CHECKDB is used to check whether corruption has occurred in the database. At the same time, try to repair the database damage, so that the database can be accessed normally again.

DBCC CHECKDB; Or DBCC CHECKDB (); Check the current database.

DBCC CHECKDB WITH NO_INFOMSGS; Disallow informational messages so that only error or exception messages can be retrieved.

The with TABLERESULTS parameter is used to display the results of the DBCC file as a table.

DBCC CHECKDB([AdventureWorks2016]) with TABLERESULTS;
Copy the code

Get one of the DBCC results or collect the DBCC results into a table

Collecting DBCC results into tables is more about getting tabular results. Especially in programming development, the result data of DBCC needs to be obtained. It can be inserted into a table or temporary table first, and then SLECT can query the table to obtain the result and a certain data item in the result.

INSERT INTO

EXECUTE (‘DBCC XXX ‘); , you can EXECUTE DBCC commands as dynamic SQL strings in EXEC/EXECUTE and insert the results into a table or temporary table so that an item of data can be queried based on criteria.

Example 1: Execute the DBCC CHECKDB command and insert the result into a table or temporary table

Create a temporary table (or table) as follows.

DECLARE @Database_Name NVARCHAR(50)
SET @Database_Name = 'master';

CREATE TABLE #DBCC
(
  [Error] VARCHAR(255) ,
  [Level] VARCHAR(255) ,
  [State] VARCHAR(255) ,
  [MessageText] VARCHAR(255) ,
  [RepairLevel] VARCHAR(255) ,
  [Status] VARCHAR(255) ,
  [DBId] VARCHAR(255) ,
  [DBFragId] VARCHAR(255) ,
  [ObjectId] VARCHAR(255) ,
  [IndexId] VARCHAR(255) ,
  [PartitionId] VARCHAR(255) ,
  [AllocUnitId] VARCHAR(255) ,
  [RIdDBId] VARCHAR(255) ,
  [RIdPruId] VARCHAR(255) ,
  [File] VARCHAR(255) ,
  [Page] VARCHAR(255) ,
  [Slot] VARCHAR(255) ,
  [RefDBId] VARCHAR(255) ,
  [RefPruId] VARCHAR(255) ,
  [RefFile] VARCHAR(255) ,
  [RefPage] VARCHAR(255) ,
  [RefSlot] VARCHAR(255) ,
  [Allocation] VARCHAR(255));INSERT INTO #DBCC
             ( Error ,
               [Level] ,
               [State] ,
               MessageText ,
               RepairLevel ,
               [Status] ,
               [DBId] ,
               DBFragId ,
               ObjectId ,
               IndexId ,
               PartitionId ,
               AllocUnitId ,
               RIdDBId ,
               RIdPruId ,
               [File] ,
               [Page] ,
               Slot ,
               RefDBId ,
               RefPruId ,
               RefFile ,
               RefPage ,
               RefSlot ,
               Allocation )
EXEC ('DBCC CHECKDB ([' + @Database_Name + ']) WITH ALL_ERRORMSGS, TABLERESULTS, NO_INFOMSGS; ' );
SELECT * FROM #DBCC;

-- 'NO_INFOMSGS' can be added as needed to disable informational messages.
Copy the code

Example 2: Execute the DBCC CHECKCONSTRAINTS command and insert the result into a table

create table DBCC_Check_Constratints
(
Table_Name varchar(128),
Constraint_Name varchar(128),
Where_Location varchar(255));-- DBCC CHECKCONSTRAINTS('[dbo].[AWBuildVersion]') WITH ALL_CONSTRAINTS;
INSERT INTO DBCC_Check_Constratints EXEC('DBCC CHECKCONSTRAINTS([AWBuildVersion]); ');
select * from DBCC_Check_Constratints;
drop table DBCC_Check_Constratints;
Copy the code

DBCC CHECKCONSTRAINTS (table_name | table_id | constraint_name | constraint_id) to check the current database specified in the table on the integrity of the specified or all constraints. That is, constraints on the validation table.

Because DBCC CHECKCONSTRAINTS returns the name of the constraint that violates the constraint, it usually returns an empty result set if there are no problems.

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS; Checks the integrity of all enabled and disabled constraints on all tables in the current database.

Example 3: Receive the execution results of DBCC USEROPTIONS using table variables

Declare @T table (Options varchar(100),Value varchar(100));
Insert Into @T 
Exec('DBCC USEROPTIONS');

Get the current user's time format
Select * 
 From @T 
 Where Options ='dateformat';

Gets the isolation level of the current connection
Select * 
 From @T 
 Where Options ='isolation level';
Copy the code

Example 4: Use the system view or table instead of running the DBCC SHOWFILESTATS command to obtain information

The DBCC SHOWFILESTATS command displays information about database files, such as the total space and used space. For those who want to obtain the same information, or need to use SELECT to return data to the application. You can use an alternative, such as using the sys.database_files database view to get the same information.

DBCC SHOWFILESTATS;
Copy the code

Execute ‘DBCC showFilestats’ dynamic SQL to fetch data into a table variable, and then calculate the percentage of free data file space in the database:

declare @FileStats table
(
  Fileid int,
  [FileGroup] int,
  TotalExtents int,
  UsedExtents int,
  Name varchar(255),
  [FileName] varchar(max)
)
insert into @FileStats execute('dbcc showfilestats');

select  1-convert(float.sum(UsedExtents))/convert(float.sum(TotalExtents)) as FreeDataSpace from @FileStats;
Copy the code

The alternative is to use the sys.database_files database view directly to calculate the percentage of MDF free space in the database:

-- size is the size of 8-KB pages
SELECT sum(db_f.size)/128.0 FileSizeInMB,CAST(sum(FILEPROPERTY(db_f.name, 'SpaceUsed')) AS int)/128.0 
   AS SpaceUsedInMB, convert(float, (sum(db_f.size) - sum(fileproperty(db_f.name,'SpaceUsed')))) / sum(db_f.size) NotUsedPercent
FROM sys.database_files db_f
WHERE db_f.type = 0;
Copy the code

This section is referenced from How to retrieve results from ‘DBCC SHOWFILESTATS’ with ODBC?