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.

LVL 9
JonMnyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
else
  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
begin
  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
  begin
    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 )
    begin
      select @keys = @keys + ', ' + @thiskey, @i = @i + 1
      select @thiskey = index_col(@TabName, @indid, @i)
    end
    if @indname not like '_WA%'
      insert into #TempInd values (@TabName, @indname, @status, @indid, @keys, @date)
    fetch ind_curs into @indid, @indname, @status, @date
  end
  deallocate ind_curs
  fetch next from DBTabs into @TabName
end

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

0
HainKurtSr. System AnalystCommented:
run this query

select * from sys.indexes i, sys.objects o
where i.object_id=o.object_id
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
robertg34Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.