Get DBCC IND command output into a table

Neerajprasadsharma
Posted by Neerajprasadsharma under Sql Server category on | Points: 40 | Views : 295
Creating a table with name INDOUTPUT to hold the dbcc IND output into a table.

CREATE TABLE INDOUTPUT
(

DB VARCHAR (50) , Table_Name VARCHAR (250) ,
PageFID BIGINT ,PagePID BIGINT , IAMFID VARCHAR (MAX) , IAMPID VARCHAR (MAX) , ObjectID VARCHAR (MAX) ,
IndexID BIGINT ,PartitionNumber VARCHAR (MAX) , PartitionID BIGINT,
iam_chain_type VARCHAR (MAX) ,PageType BIGINT , IndexLevel BIGINT , NextPageFID BIGINT ,
NextPagePID BIGINT , PrevPageFID BIGINT, PrevPagePID BIGINT, RefreshTime
DateTime Default (Getdate()) )



This store procedure will call the DBCC IND command with the required database name, table and index name,


CREATE PROCEDURE GET_IND_OUTPUT (@DNAME VARCHAR (50) , @TNAME VARCHAR (50) , @INDEXID INT )
AS
BEGIN
DBCC IND (@DNAME, @TNAME, @INDEXID) WITH NO_INFOMSGS

END


Creating a stored procedure which will delete all the previous inserted result and insert a new result.

CREATE PROCEDURE REFRESH_INDOUTPUT (@DNAME VARCHAR (50) , @TNAME VARCHAR (250) , @INDEXID INT )
AS
BEGIN
SET NOCOUNT ON
DELETE FROM INDOUTPUT
INSERT INTO INDOUTPUT (PageFID ,PagePID , IAMFID , IAMPID , ObjectID ,IndexID ,PartitionNumber , PartitionID , iam_chain_type ,PageType , IndexLevel , NextPageFID , NextPagePID , PrevPageFID , PrevPagePID)
EXEC GET_IND_OUTPUT @DNAME , @TNAME , @INDEXID

IF( @@ERROR=0)
BEGIN PRINT 'INDOUTPUT REFRESHED' END
UPDATE INDOUTPUT set DB =@DNAME , Table_Name = @TNAME ,INDEXID = @INDEXID

END




Example:
-                                      ----Database Name,      Table name, Index Id
EXEC REFRESH_INDOUTPUT 'TutorialSQLServer', 'DBO.IDEALCI', 1

Comments or Responses

Login to post response