MSSQL 인덱스 관련 파일, 페이지 번호 보기 (DBA 관리용 시스템 프로시저)

/************************************************************************************************

-- sp_converthex0

-- 해당 오브젝트의 인덱스 관련 파일과 페이지번호를 전체 출력한다.

************************************************************************************************/

USE MASTER

GO

IF OBJECT_ID('sp_converthex0') IS NOT NULL

   DROP PROCEDURE sp_converthex0

GO

CREATE PROC sp_converthex0 

  @objname VARCHAR(100) = NULL

AS

  SET NOCOUNT ON

  IF (@objname IS NULL)

  BEGIN

       RAISERROR('''@objname'' parameter is empty... ', 16, 1)

       RETURN(1)

  END

  IF NOT EXISTS(SELECT ID

                FROM sysobjects

                WHERE id = object_id(@objname)

                  AND xtype IN ('S','U', 'V')

               )

  BEGIN

     RAISERROR('There is no ''%s'' object(xtype:s,u,v) in this database...', 16, 1,@objname)

     RETURN(1)

  END

  SELECT  convert(char(30), name) AS 'object_name', 

id, 

indid, 

convert(varchar(2), (convert(int, substring(first, 6, 1)) * power(2, 8)) 

                    + (convert(int, substring(first, 5, 1)))) + ':' +

convert(varchar(11), 

(convert(int, substring(first, 4, 1)) * power(2, 24)) + 

(convert(int, substring(first, 3, 1)) * power(2, 16)) + 

(convert(int, substring(first, 2, 1)) * power(2, 8)) + 

(convert(int, substring(first, 1, 1)))) AS "firstDec",

first,

convert(varchar(2), (convert(int, substring(root, 6, 1)) * power(2, 8)) 

                    + (convert(int, substring(root, 5, 1)))) + ':' +

convert(varchar(11), 

(convert(int, substring(root, 4, 1)) * power(2, 24)) + 

(convert(int, substring(root, 3, 1)) * power(2, 16)) + 

(convert(int, substring(root, 2, 1)) * power(2, 8)) + 

(convert(int, substring(root, 1, 1)))) AS "rootDec",

root,

convert(varchar(2), (convert(int, substring(firstIAM, 6, 1)) * power(2, 8)) 

                    + (convert(int, substring(firstIAM, 5, 1)))) + ':' +

convert(varchar(11), 

(convert(int, substring(firstIAM, 4, 1)) * power(2, 24)) + 

(convert(int, substring(firstIAM, 3, 1)) * power(2, 16)) + 

(convert(int, substring(firstIAM, 2, 1)) * power(2, 8)) + 

(convert(int, substring(firstIAM, 1, 1)))) AS "firstIAMDec",

firstIAM

  FROM sysindexes

  WHERE id = object_id(@objname)

  RETURN(0) --sp_converthex0

  SET NOCOUNT OFF

GO

  

GRANT EXECUTE ON sp_converthex0 TO PUBLIC

GO