Index Research

gentle_table_index_research.sql

 DECLARE @object_id INT = OBJECT_ID('dbo.YourTable');
  DECLARE @db_id INT = DB_ID();
  DECLARE @index_id INT;
  DECLARE @index_name NVARCHAR(128);

  DROP TABLE IF EXISTS #IndexStats
  SELECT TOP 0 *
  INTO #IndexStats
  FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, 'LIMITED');

   -- Cursor to iterate through indexes
  DECLARE index_cursor CURSOR LOCAL FAST_FORWARD FOR
      SELECT
          i.index_id,
          i.name AS index_name
      FROM sys.tables t
      JOIN sys.indexes i ON t.object_id = i.object_id
      WHERE t.object_id = @object_id
        AND i.index_id > 0  -- Exclude heap (0)
      ORDER BY i.index_id;

  OPEN index_cursor;
  FETCH NEXT FROM index_cursor INTO @index_id, @index_name;

  WHILE @@FETCH_STATUS = 0
  BEGIN
      PRINT 'Processing index: ' + ISNULL(@index_name, 'NULL') + ' (index_id: ' + CAST(@index_id AS VARCHAR(10)) + ')';

      INSERT INTO #IndexStats
      SELECT *
      FROM sys.dm_db_index_physical_stats(@db_id, @object_id, @index_id, NULL, 'LIMITED');

      PRINT '  Rows inserted: ' + CAST(@@ROWCOUNT AS VARCHAR(10));

      FETCH NEXT FROM index_cursor INTO @index_id, @index_name;

      IF @@FETCH_STATUS = 0
      BEGIN
          WAITFOR DELAY '00:00:01';  -- 1 second delay (adjust as needed)
      END
  END

  CLOSE index_cursor;
  DEALLOCATE index_cursor;

  SELECT
      s.*,
      i.name AS index_name
  FROM #IndexStats s
  LEFT JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
  ORDER BY s.index_id, s.partition_number;