Transact-SQL: Return DBCC Output in a View
Background
This is a very short article about a small trick, to use DBCC result in a View element. This article based on an answer to a question in MSDN forum, which will be our basic case study for the article.
Our case study is the Original Forum question
"I'm returning Data from the Blocked Process Report and want to show which resource is being blocked. For certain locks means I need to find the ObjectID from the PageID. Ideally I would like to do this using a function or view in order to access it within the query. And of course DBCC Page cannot be used in a view or Function."
Is this correct?
The solution
Since we cannot directly use DBCC in a view, we are going to use a simple workaround based on several ideas: (1) We can catch the output of a DBCC-Statement in a temporary table or table variable. (2) We can create a system stored procedure which uses and returns temporary table content. (3) We can execute a stored procedure in a view using openquery. Let's start
Step 1: create a system stored procedure that returns DBCC
System stored procedures are created and stored in the master database and have the sp_ prefix.
USE MASTER
GO
-- we need this value for the PROCEDURE if we want to use "EXECUTE AS" hint
select SUSER_NAME()
GO
-- We can use table variable or temporary table in SP
;CREATE PROCEDURE sp_Ari_DbccPage
(@_DatabaseName nvarchar(50),
@_FileID int,
@_PageID int,
@_DescriptionLevel int)
-- WITH EXECUTE AS 'user from previous query'
-- WITH EXECUTE AS OWNER
-- WITHOUT LOGIN
AS
SET NOCOUNT ON
-- We can insert DBCC result to table variable (this i first step of the trick)
-- We could use Temporary table as well, then if need we can add index
DECLARE @MyPageTbl as TABLE(ParentObject VARCHAR(128), [Object] VARCHAR(512), Field VARCHAR(128), [Value] VARCHAR(8000) )
Declare @Query NVARCHAR(MAX) =
'DBCC PAGE(
''' + convert(nvarchar(max),@_DatabaseName) + ''','
+ convert(nvarchar(max),@_FileID) + ','
+ convert(nvarchar(max),@_PageID) + ','
+ convert(nvarchar(max),@_DescriptionLevel)
+ ') WITH TABLERESULTS'
Insert @MyPageTbl EXEC (@Query)
select * from @MyPageTbl
GO
-- Let's check our SP:
exec sp_Ari_DbccPage
@_DatabaseName = 'MSSQLTIPS',
@_FileID = 1,
@_PageID = 89,
@_DescriptionLevel = 3
GO
-- Mark our stored procedure as system object
EXEC sp_ms_marksystemobject 'sp_Ari_DbccPage'
GO
Step 2: Preparation
In this step we are going to create a new database. In that database we will create a table and then examine the pages used by the table using the DBCC IDN. We will use this information next step, in order to create the view that returns the result of DBCC page.
 ImportantI |
|---|
Notice: We can use the same trick and build a SP that returns the DBCC IDN result or even combine SP which will check DBCC IDN and then execute DBCC PAGE according to the result. |
------------------------------------------------------------ DDL
USE MASTER
GO
CREATE DATABASE Ari_DbccPage_DB
GO
USE Ari_DbccPage_DB
GO
CREATE TABLE Ari_DbccPage_Tbl (
UsersID INT IDENTITY(1,1),
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Registertime DATETIME NULL,
CONSTRAINT PK_User PRIMARY KEY (UsersID), -- This wil be a CLUSTERED index
CONSTRAINT UQ_FirstName_LastName UNIQUE (FirstName, LastName) -- This wil be a NONCLUSTERED index
)
GO
------------------------------------------------------------ DML
INSERT INTO Ari_DbccPage_Tbl (FirstName,LastName,Registertime)
values
('A','I','2014-02-27'),
('S','U','2014-02-27'),
('D','Y','2014-02-27'),
('F','H','2014-02-27'),
('G','B','2014-02-27'),
('H','G',NULL),
('Z','V',NULL),
('X','C','2014-02-27')
GO
select * from Ari_DbccPage_Tbl
GO
-- find where the pages for this table and its data live
-- DBCC IND use to list all of a table's data and index pages
-- DBCC IND (database_name, table_name, index_id)
DBCC IND('Ari_DbccPage_DB',Ari_DbccPage_Tbl,-1) -- index_id -1: get all index_id
GO
/*
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 121 NULL NULL 293576084 1 1 72057594040680448 In-row data 10 NULL 0 0 0 0
1 120 1 121 293576084 1 1 72057594040680448 In-row data 1 0 0 0 0 0
1 127 NULL NULL 293576084 2 1 72057594040745984 In-row data 10 NULL 0 0 0 0
1 126 1 127 293576084 2 1 72057594040745984 In-row data 2 0 0 0 0 0
*/
-- What does all this data mean?
---------------------------------
-- PagePID represent a file number
-- ndexID is the index_id as found in sys.indexes
select * from sys.indexes where object_id = OBJECT_ID('Ari_DbccPage_DB.DBO.Ari_DbccPage_Tbl')
-- PageType
-- PageType = 1 is a data page,
-- PageType = 2 is an index page
-- PageType = 10 is the IAM page that maintains the index itself.
-- We can notice in our case that we have two IndexID 1,2 and each index have one PageType = 10 and one which is 1,2
-- IndexLevel is the level within the IAM structure the page falls.
-- IndexLevel = 0, then this is a leaf level page for the index.
-- Using the value of MAX(IndexLevel) we can estimate the IO use when we need to get a specific row
-- DBCC PAGE command allows to examine the contents of data and index pages.
-- DBCC PAGE (database_name,file_number,page_number,information_level)
-- Note: Before we can run DBCC PAGE, it's required that trace flag 3604 be set to instruct the engine to send output to the console;
-- otherwise you won't see anything!
DBCC TRACEON(3604)
DBCC PAGE('Ari_DbccPage_DB',1,120,3) WITH TABLERESULTS
GO
Step 3: Return stored procedure result in view, using openquery
Now we can build a new view in our database and get the DBCC information from the SP using openquery statement.
-- Move to any local database
Use Ari_DbccPage_DB
GO
-- get server name
select name from sys.servers
GO
-- check that we can use openquery to execute our stored procedure
select * from openquery(
[use our server name from previous query],
'sp_Ari_DbccPage
@_DatabaseName = ''Ari_DbccPage_DB'',
@_FileID = 1,
@_PageID = 120,
@_DescriptionLevel = 3'
)
GO
-- That is all :-) Now we can use our DBCC in a view
create view Ari_DbccPage_View as
select * from openquery(
[use our server name from previous query],
'master.dbo.sp_Ari_DbccPage
@_DatabaseName = ''MSSQLTIPS'',
@_FileID = 1,
@_PageID = 89,
@_DescriptionLevel = 3'
)
go
-- Check our view
select * from Ari_DbccPage_View
-- clean local database (we do not clean the system SP for future use)
drop view Ari_DbccPage_View
 Note! |
|---|
Conflicts might be well avoided by following good naming convention. For example (1) names might start with "Microsoft SQL_" or something similar. It is not likely users will use something like this. (2) Using "Stored_Procedure" as part of the name will probably implies that this is a stored procedure element, and it is not likely that it will used for different element type. (3) Name should describe the element (what it does). It is likely that element with the name "Split_String" is doing some type of splitting. |
Conclusions
As we have seen there is no problem to display in VIEW, the results of executing any DBCC statement, using stored procedure and openquery.
Resources and More information
Code
- The full code with all the scripts can be downloaded from the Gallery here:
http://gallery.technet.microsoft.com/Transact-SQL-Return-DBCC-b786b497
Resource
- This article is a translation from Hebrew to English of the article by Ronen Ariely at:
http://ariely.info/Blog/tabid/83/EntryId/150/SQL-Server-Tricks-Return-DBCC-output-in-a-View.aspx - This case study based on MSDN forum question at
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/750a9d9a-5d71-44bd-a578-3d8a233d5e42/objectid-from-pageid?forum=transactsql#3c4ecddc-50ae-4ee5-b111-e441a8adae63
More information
- DBCC (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188796.aspx - Using DBCC PAGE to Examine SQL Server Table and Index Data
http://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/
See Also
- SQL Server Query Language - Transact-SQL
- SQL Server tricks: Shrink SQL Database During Restore