Monday 30 May 2011

INDEX REBUILD DATE FIND

Declare @objname varchar(30),
    @indname varchar(30),
    @objid varchar(30),
    @indid varchar(4)
Print 'DATABASE: '+upper(DB_NAME())
declare list_Cursor cursor for
select name,object_id(name)from sysobjects where xtype='U'
open list_cursor
fetch next from list_cursor into @objname,@objid
while @@fetch_status =0
Begin
    print 'object name:'+upper(@objname)+'  '+'Object ID: '+@objid
    declare list_indcursor cursor for select name,indid from sysindexes where id=@objid and indid not in (0,255)
    open list_indcursor
    fetch next from list_indcursor into @indname,@indid
    while @@fetch_status=0
    Begin
        print '             '+'Index name:  '+@indname+'   Index rebuilded on :  '+convert(varchar(25),STATS_DATE (@objid , @indid))
        fetch next from list_indcursor into @indname,@indid
    End
    fetch next from list_cursor into @objname,@objid
    CLOSE list_indcursor
    DEALLOCATE list_indcursor
End
CLOSE list_cursor
DEALLOCATE list_cursor

No comments: