Index properties with a kick

The following function is an expansion to the Microsoft SQL Server's built-in function INDEXPROPERTY.

It returns a table containing table names, index names, property names and property values, so it can be used for analysis and documentation purposes.

create function dbo.fnList_IndexProperty
 (
 @tableName sysname  = null
 ,@indexName nvarchar(128) = null
 ,@propertyName varchar(128) = null
 )
returns @Result table
  (
  ResultID bigint identity (1, 1) primary key
  ,TableName sysname
  ,IndexName nvarchar(128)
  ,PropertyName varchar(128)
  ,PropertyValue int
  )
as
begin
 declare @objectList table
    (
    ListID  bigint identity (1, 1) primary key
    ,TableID int
    ,TableName sysname
    ,IndexName nvarchar(128)
    )
 declare @startID bigint
 declare @endID  bigint
 declare @curID  bigint
 declare @tableId int
 declare @curTableName sysname
 declare @curIndexName nvarchar(128)

 set @tableId = object_id(@tableName)

 if (@tableName is null)
  and (@indexName is null)
  begin
   insert @objectList
    (
    TableID
    ,TableName
    ,IndexName
    )
    select dbo.sysobjects.[id] as TableID
     ,INFORMATION_SCHEMA.TABLES.TABLE_NAME as TableName
     ,dbo.sysindexes.[name] as IndexName
     from INFORMATION_SCHEMA.TABLES
      inner join dbo.sysobjects
        on dbo.sysobjects.[name] = INFORMATION_SCHEMA.TABLES.TABLE_NAME
      inner join dbo.sysindexes
        on dbo.sysindexes.[id] = dbo.sysobjects.[id]
  end
 else
  begin
   if (@tableName is not null)
    and (@indexName is null)
    begin
     insert @objectList
      (
      TableID
      ,TableName
      ,IndexName
      )
      select dbo.sysobjects.[id] as TableID
       ,INFORMATION_SCHEMA.TABLES.TABLE_NAME as TableName
       ,dbo.sysindexes.[name] as IndexName
       from INFORMATION_SCHEMA.TABLES
        inner join dbo.sysobjects
          on dbo.sysobjects.[name] = INFORMATION_SCHEMA.TABLES.TABLE_NAME
          inner join dbo.sysindexes
            on dbo.sysindexes.[id] = dbo.sysobjects.[id]
       where (dbo.sysobjects.[id] = @tableId)
    end

   if (@tableName is null)
    and (@indexName is not null)
    begin
     insert @objectList
      (
      TableID
      ,TableName
      ,IndexName
      )
      select dbo.sysobjects.[id] as TableID
       ,INFORMATION_SCHEMA.TABLES.TABLE_NAME as TableName
       ,dbo.sysindexes.[name] as IndexName
       from INFORMATION_SCHEMA.TABLES
        inner join dbo.sysobjects
          on dbo.sysobjects.[name] = INFORMATION_SCHEMA.TABLES.TABLE_NAME
          inner join dbo.sysindexes
            on dbo.sysindexes.[id] = dbo.sysobjects.[id]
       where (dbo.sysindexes.[name] = @indexName)
    end

   if (@tableName is not null)
    and (@indexName is not null)
    begin
     insert @objectList
      (
      TableID
      ,TableName
      ,IndexName
      )
      select dbo.sysobjects.[id] as TableID
       ,INFORMATION_SCHEMA.TABLES.TABLE_NAME as TableName
       ,dbo.sysindexes.[name] as IndexName
       from INFORMATION_SCHEMA.TABLES
        inner join dbo.sysobjects
          on dbo.sysobjects.[name] = INFORMATION_SCHEMA.TABLES.TABLE_NAME
          inner join dbo.sysindexes
            on dbo.sysindexes.[id] = dbo.sysobjects.[id]
       where (dbo.sysobjects.[id] = @tableId)
        and (dbo.sysindexes.[name] = @indexName)
    end
  end

 select @startID
   = min(objectList.ListID)
  ,@endID
   = max(objectList.ListID)
  from @objectList objectList

 set @curID = @startID

 while (@curID <= @endID)
  begin
   select @curTableName
     = objectList.TableName
    ,@tableId
     = objectList.TableID
    ,@curIndexName
     = objectList.IndexName
    from @objectList objectList
    where (objectList.ListID = @curID)

   if (@propertyName is null)
    begin
     insert @Result
      (
      TableName
      ,IndexName
      ,PropertyName
      ,PropertyValue
      )
      select @curTableName as TableName
       ,@curIndexName as IndexName
       ,'IndexDepth' as PropertyName
       ,indexproperty(@tableId, @curIndexName, 'IndexDepth') as PropertyValue
      union
      select @curTableName
       ,@curIndexName
       ,'IndexFillFactor'
       ,indexproperty(@tableId, @curIndexName, 'IndexFillFactor')
      union
      select @curTableName
       ,@curIndexName
       ,'IndexID'
       ,indexproperty(@tableId, @curIndexName, 'IndexID')
      union
      select @curTableName
       ,@curIndexName
       ,'IsAutoStatistics'
       ,indexproperty(@tableId, @curIndexName, 'IsAutoStatistics')
      union
      select @curTableName
       ,@curIndexName
       ,'IsClustered'
       ,indexproperty(@tableId, @curIndexName, 'IsClustered')
      union
      select @curTableName
       ,@curIndexName
       ,'IsFulltextKey'
       ,indexproperty(@tableId, @curIndexName, 'IsFulltextKey')
      union
      select @curTableName
       ,@curIndexName
       ,'IsHypothetical'
       ,indexproperty(@tableId, @curIndexName, 'IsHypothetical')
      union
      select @curTableName
       ,@curIndexName
       ,'IsPadIndex'
       ,indexproperty(@tableId, @curIndexName, 'IsPadIndex')
      union
      select @curTableName
       ,@curIndexName
       ,'IsPageLockDisallowed'
       ,indexproperty(@tableId, @curIndexName, 'IsPageLockDisallowed')
      union
      select @curTableName
       ,@curIndexName
       ,'IsRowLockDisallowed'
       ,indexproperty(@tableId, @curIndexName, 'IsRowLockDisallowed')
      union
      select @curTableName
       ,@curIndexName
       ,'IsStatistics'
       ,indexproperty(@tableId, @curIndexName, 'IsStatistics')
      union
      select @curTableName
       ,@curIndexName
       ,'IsUnique'
       ,indexproperty(@tableId, @curIndexName, 'IsUnique')
    end
   else
    begin
     insert @Result
      (
      TableName
      ,IndexName
      ,PropertyName
      ,PropertyValue
      )
      select @curTableName as TableName
       ,@curIndexName as IndexName
       ,@propertyName as PropertyName
       ,indexproperty(@tableId, @curIndexName, @propertyName) as PropertyValue
    end

   set @curID = @curID + 1
  end

 return
end
go

A few examples of use:

• to show all properties for all indexes on all tables in the current database:

  select IP.TableName as TableName
   ,IP.IndexName as IndexName
   ,IP.PropertyName as PropertyName
   ,IP.PropertyValue as PropertyValue
   from dbo.fnList_IndexProperty
          (null, null, null) IP

• to show the IsClustered property for all indexes on all tables in the current database:

  select IP.TableName as TableName
   ,IP.IndexName as IndexName
   ,IP.PropertyName as PropertyName
   ,IP.PropertyValue as PropertyValue
   from dbo.fnList_IndexProperty
          (null, null, 'IsClustered') IP

• to show the IsUnique property for all indexes named "taspnet_Profile" on all tables in the current database:

  select IP.TableName as TableName
   ,IP.IndexName as IndexName
   ,IP.PropertyName as PropertyName
   ,IP.PropertyValue as PropertyValue
   from dbo.fnList_IndexProperty
          (null, 'taspnet_Profile', 'IsUnique') IP

ML