--
-- ObjectInfo, PageInfo, TableInfo and IndexInfo
-- Copyright 2012, Sebastian Meine, sqlity.net
-- Distribution or publication without prior written consent prohibited
--
--
-- These procedures use alot of resources as well as some undocumented features
--
-- !!DO NOT USE IN PRODUCTION!!
--
--  Usage:
-- EXEC dbo.TableInfo 'dbo.MyTable';
-- EXEC dbo.IndexInfo 'dbo.MyTable','NameOfSomeIndexOnMyTable';
--
--
--
CREATE PROCEDURE dbo.ObjectInfo
    (
      @TableObjId INT,
      @IndexId INT
    )
AS 
    BEGIN
        DECLARE @TableName NVARCHAR(MAX) = OBJECT_SCHEMA_NAME(@TableObjId)+'.'+OBJECT_NAME(@TableObjId);
        SELECT  @TableName TblName ,
         i.name IdxName,
         i.type_desc IdxType,
                au.allocation_unit_id ,
                au.type ,
                au.type_desc ,
                au.container_id ,
                au.filegroup_id ,
                au.total_pages ,
                au.used_pages ,
                au.data_pages ,
                ( SELECT    CAST(file_id AS NVARCHAR(MAX)) + ':'
                            + CAST(page_id AS NVARCHAR(MAX))
                  FROM      sys.fn_PhysLocCracker(au.first_page)
                ) first_page ,
                ( SELECT    CAST(file_id AS NVARCHAR(MAX)) + ':'
                            + CAST(page_id AS NVARCHAR(MAX))
                  FROM      sys.fn_PhysLocCracker(au.root_page)
                ) root_page ,
                ( SELECT    CAST(file_id AS NVARCHAR(MAX)) + ':'
                            + CAST(page_id AS NVARCHAR(MAX))
                  FROM      sys.fn_PhysLocCracker(au.first_iam_page)
                ) first_iam_page,i.is_unique
        FROM    sys.system_internals_allocation_units au
                JOIN sys.partitions p ON au.container_id = p.partition_id
                JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
        WHERE   p.object_id = @TableObjId AND i.index_id = @IndexId;
  -----------------
        SELECT  index_type_desc ,
                Alloc_unit_type_desc ,
                index_depth ,
                index_level ,
                page_count ,
                record_count ,
                avg_record_size_in_bytes
        FROM    sys.dm_db_index_physical_stats(DB_ID(), @TableObjId,
                                               @IndexId, NULL, 'DETAILED') ;
        CREATE TABLE #tablepages
            (
              PageFID TINYINT ,
              PagePID INT ,
              IAMFID TINYINT ,
              IAMPID INT ,
              ObjectID INT ,
              IndexID TINYINT ,
              PartitionNumber TINYINT ,
              PartitionID BIGINT ,
              iam_chain_type VARCHAR(30) ,
              PageType TINYINT ,
              IndexLevel TINYINT ,
              NextPageFID TINYINT ,
              NextPagePID INT ,
              PrevPageFID TINYINT ,
              PrevPagePID INT ,
              PRIMARY KEY CLUSTERED ( PageFID, PagePID )
            ) ;
        DECLARE @cmd NVARCHAR(MAX) = 'DBCC IND(0,''' + @TableName
                      + ''','+CAST(@IndexId AS NVARCHAR(MAX))+') WITH NO_INFOMSGS;'
        INSERT  INTO #tablepages
                EXEC(@cmd);
                
        SELECT  CAST(PageFID AS NVARCHAR(MAX)) + ':'
                + CAST(PagePID AS NVARCHAR(MAX)) Page ,
                CAST(NextPageFID AS NVARCHAR(MAX)) + ':'
                + CAST(NextPagePID AS NVARCHAR(MAX)) NextPage ,
                CAST(PrevPageFID AS NVARCHAR(MAX)) + ':'
                + CAST(PrevPagePID AS NVARCHAR(MAX)) PrevPage ,
                iam_chain_type ,
                PageType ,
                IndexLevel ,
                CAST(IAMFID AS NVARCHAR(MAX)) + ':'
                + CAST(IAMPID AS NVARCHAR(MAX)) IAMPage,
                'EXEC dbo.PageInfo '+CAST(PageFID AS NVARCHAR(MAX)) + ','
                + CAST(PagePID AS NVARCHAR(MAX)) +';' DbccCmd
        FROM    #tablepages
        ORDER BY PageType DESC ,
                IndexLevel DESC ,
                PagePID ASC ;
                
    END;
GO

CREATE PROCEDURE PageInfo(@FileId INT,@PageId INT,@Type INT = NULL)
AS
BEGIN
  DECLARE @cmd NVARCHAR(MAX) = 'DBCC TRACEON(3604);DBCC PAGE('''+DB_NAME()+''','+CAST(@FileId AS NVARCHAR(MAX))+','+CAST(@PageId AS NVARCHAR(MAX))+',';
  IF(@Type IS NULL)
  BEGIN
    EXEC(@cmd+'3);');
    EXEC(@cmd+'2);');
  END
  ELSE
  BEGIN
    SET @cmd += CAST(@Type AS NVARCHAR(MAX))+');';
    EXEC(@cmd);
  END;
END;

GO

CREATE PROCEDURE dbo.TableInfo(@TableName NVARCHAR(MAX))
AS
BEGIN
  DECLARE @TableObjId INT = OBJECT_ID(@TableName);
  DECLARE @IndexId INT = (SELECT index_id FROM sys.indexes WHERE index_id<2 AND OBJECT_ID = @TableObjId);
  EXEC dbo.ObjectInfo @TableObjId,@IndexId;
                
  EXEC('SELECT (SELECT CAST(file_id AS NVARCHAR(MAX))+'':''+CAST(page_id AS NVARCHAR(MAX))+'':''+CAST(slot_id AS NVARCHAR(MAX)) FROM sys.fn_PhysLocCracker(%%physloc%%)) _PhysLoc_,* FROM '+@TableName+';'); 
END;
GO

CREATE PROCEDURE dbo.IndexInfo(@TableName NVARCHAR(MAX),@IndexName NVARCHAR(MAX))
AS
BEGIN
  DECLARE @TableObjId INT = OBJECT_ID(@TableName);
  DECLARE @IndexId INT = (SELECT index_id FROM sys.indexes WHERE name = @IndexName AND OBJECT_ID = @TableObjId);
  EXEC dbo.ObjectInfo @TableObjId,@IndexId;
END;
GO