• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

sql server show list of indexes and created /modified date

is it possible to get a list of indexes from sql server and show the created and modified dates?

I am using sql server 2005 but the database is still running 2000 compatibility mode.

1 Solution
lcohanDatabase AnalystCommented:
Here try this:

declare @indid smallint,      -- the index id of an index
      @IndName sysname,
      @status int,
      @keys nvarchar(2078),-- string build index key list, length = (16*max_id_length)+(15*2)
      @objid int,
      @dbname      sysname,
      @TabName sysname,
      @TableName sysname,
      @TabToSearch sysname,
    @index_key nvarchar(2078),
    @date datetime

--set @TableName = 'contact'

if @TableName is not null
  set @TabToSearch = @TableName
  set @TabToSearch = '%'

create table #TempInd (TableName sysname not null, index_name sysname NOT NULL, stats int,indid int, index_keys nvarchar(2078) NOT NULL, last_update datetime)

declare DBTabs cursor fast_forward for

select name
      from sysobjects
      where type = 'U' and name like @TabToSearch
      order by name

open DBTabs
fetch next from DBTabs into @TabName
while @@fetch_status = 0
  declare ind_curs insensitive cursor for
    select i.indid, i.name, i.status,  STATS_DATE(object_id, s.stats_id) AS last_update
 from sysindexes i, sys.stats s
      where i.id = object_id(@Tabname) and i.indid > 0 and i.indid < 255 and i.name = s.name and s.[object_id] = i.id
--        and (stats & 32)=0 and (stats & 64)=0 and (stats & 8388608)=0 and (stats & 16777216)=0
    order by i.indid
  open ind_curs
  fetch ind_curs into @indid, @indname, @status, @date
  while @@fetch_status >= 0
    declare @i int, @thiskey sysname
    select @keys = index_col(@TabName, @indid, 1),
      @i = 2, @thiskey = index_col(@TabName, @indid, 2)
    while (@thiskey is not null )
      select @keys = @keys + ', ' + @thiskey, @i = @i + 1
      select @thiskey = index_col(@TabName, @indid, @i)
    if @indname not like '_WA%'
      insert into #TempInd values (@TabName, @indname, @status, @indid, @keys, @date)
    fetch ind_curs into @indid, @indname, @status, @date
  deallocate ind_curs
  fetch next from DBTabs into @TabName

deallocate DBTabs

delete from #TempInd where (stats&2048)<>0 or (stats&4096)<>0
select * into #IXlist from #TempInd
--      where --indid=1 -- no clustered indexes
--            stats = 2 -- unique values
select * from #IXlist

drop table #TempInd
drop table #IXlist

HainKurtSr. System AnalystCommented:
run this query

select * from sys.indexes i, sys.objects o
where i.object_id=o.object_id
select so.name, so.create_date, so.modify_date from sys.indexes si inner join sys.objects so
on si.object_id = so.object_id
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now