InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 12:30:39 GMT20http://www.sqlservercentral.com/Forums/Topic884125-391-1.aspxThanks a lot. In my quick reference book now. unfortunately, some companies still using 2000 who still not able to decide either move to 2005 or 2008....lol :-D:-D:-D:-D:-D:-DSat, 20 Mar 2010 03:34:28 GMTcrazy4sqlhttp://www.sqlservercentral.com/Forums/Topic884125-391-1.aspxVery nice Ron!Can't believe anyone is still using 2000 though ;-) :-P(Please no-one take that statement seriously - thanks)Fri, 19 Mar 2010 21:40:35 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic884125-391-1.aspxThis code works in SQL 2000[code="sql"]SELECT 'Index Name' = i.name, OBJECT_NAME(i.id) AS 'Table Name', Case Indid WHEN 1 THEN 'Clustered' ELSE 'NonClustered' End 'Type', 'Last Updated' = STATS_DATE(i.id, i.indid),rowmodctr AS '# Rows inserted deleted or updated', --, o.type i.keys FROM sysobjects o, sysindexes i WHERE o.id = i.id AND (o.type <> 'S' AND indid <> 0 AND indid <> 255) --Gets date of last statistics update number of rows added, deleted or updated since last update [/code]This works in SQL 2000[code="sql"]SELECT o.name AS Table_Name,i.name AS Index_Name,STATS_DATE(o.id,i.indid) AS Date_UpdatedFROM sysobjects o JOIN sysindexes i ON i.id = o.idWHERE xtype = 'U' AND i.name IS NOT NULLORDER BY o.name ASC,i.name ASC[/code]And this works in SQL 2000 - (All tested using Query Analyser - Northwind DB[code="sql"]SELECT o.name as 'Table', i.name as 'Index', c.name as 'Column', 'Index Type' = CASE WHEN PATINDEX('%_wa_sys_%',i.name) = 0 THEN 'Index' ELSE 'Statisical' END, 'Primary' = CASE WHEN (i.status & 0x800)= 0 THEN 'No' ELSE 'Yes' END, 'Clustered' = CASE WHEN (i.status & 0x10)= 0 THEN 'No' ELSE 'Yes' END, 'Unique' = CASE WHEN (i.status & 0x2) = 0 THEN 'No' ELSE 'Yes' END, 'Ignore Dup Key' = CASE WHEN (i.status & 0x1) = 0 THEN 'No' ELSE 'Yes' END, 'Ignore Dup Row' = CASE WHEN (i.status & 0x4)= 0 THEN 'No' ELSE 'Yes' END, 'No Recompute' = CASE WHEN (i.status & 0x1000000) = 0 THEN 'No' ELSE 'Yes' END, 'Computed' = CASE WHEN (c.iscomputed) = 0 THEN 'No' ELSE 'Yes' END, 'Nullable' = CASE WHEN (c.isnullable) = 0 THEN 'No' ELSE 'Yes' END, i.OrigFillFactor AS 'Orig Fill Factor', i.rowcnt as 'Est.RowCount', i.reserved * cast(8 as bigint) as ReservedKB, i.used * cast(8 as bigint) as UsedKB, t.name as 'Column Type', 'Precision' = Case c.xprec WHEN 0 THEN ' ' ELSE CAST(c.xprec as VARCHAR(3)) END, 'Scale' = Case c.xscale WHEN 0 THEN ' ' ELSE CAST(c.xscale as VARCHAR(3)) END, c.Length as 'Length', 'Updated' = STATS_DATE(i.id,i.indid) from sysobjects o with(nolock) inner join sysindexes i with(nolock) on o.id = i.id inner join sysindexkeys k with(nolock) on i.id = k.id and I.indid = K.indid inner join syscolumns c with(nolock) on k.id = c.id and K.colid = c.colid inner join systypes t with(nolock) on c.xtype = t.xtype where o.xtype <> 'S' -- Ignore system objects Order By o.name, i.name [/code]Fri, 19 Mar 2010 16:24:17 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic884125-391-1.aspxjust for curosity...what about 2000? how to check the update?Fri, 19 Mar 2010 11:46:05 GMTcrazy4sqlhttp://www.sqlservercentral.com/Forums/Topic884125-391-1.aspx[quote][b]Sandhya-371593 (3/17/2010)[/b][hr]It worked for me both on 2005 and 2008. Thanks so much !!![/quote]No worries.Wed, 17 Mar 2010 23:16:22 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic884125-391-1.aspxIt worked for me both on 2005 and 2008. Thanks so much !!!Wed, 17 Mar 2010 23:14:43 GMTSandhya-371593http://www.sqlservercentral.com/Forums/Topic884125-391-1.aspx[quote][b]Sandhya-371593 (3/16/2010)[/b][hr]Is there any way I could get the information like when the reindexing and update statistics was done on a particular database?[/quote]Sandhya,Statistics are always created and updated with an index:[code="sql"]SELECT [schema_name] = SCHEMA_NAME(T.[schema_id]), table_name = T.name, T.type_desc, index_or_statistics_name = S.name, is_auto_stats = S.auto_created, user_created = S.user_created, last_updated = STATS_DATE(T.[object_id], S.stats_id)FROM sys.tables TJOIN sys.stats S ON S.[object_id] = T.[object_id]ORDER BY T.[schema_id], T.name, S.stats_id;[/code]Wed, 17 Mar 2010 02:24:00 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic884125-391-1.aspxGRE (Gethyn Ellis) Again many thanks .... now I have learned more and I hope the person asking the original question has also learned something new.You assistance in testing is greatly appreciated.Tue, 16 Mar 2010 18:05:51 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic884125-391-1.aspx[quote][b]bitbucket-25253 (3/16/2010)[/b][hr]GRE (Gethyn Ellis) Thanks for the feed back. Edited my posting to add 2 additional scripts would you be so kind as to give them a test.Again I thank you ,and so will the original poster of the question and any others who stumble upon this forum.[/quote]Both additional Scripts run OK on 2008 against the AdventureworksDW db on SQL2008The very first script (Script 1) returned 88 rowsThe 2nd script (Script 2) returned 78 rows -- for some reason I was expecting this to more than the first script?The final script (Script 3) returns 122 rowsTue, 16 Mar 2010 17:59:16 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic884125-391-1.aspxGRE (Gethyn Ellis) Thanks for the feed back. Edited my posting to add 2 additional scripts would you be so kind as to give them a test.Again I thank you ,and so will the original poster of the question and any others who stumble upon this forum.Tue, 16 Mar 2010 17:48:06 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic884125-391-1.aspxI just ran the script on my 2008 developer edition instance and it worked fine...as long as it answers the OP's question.Tue, 16 Mar 2010 17:28:49 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic884125-391-1.aspxRefer to:Index Related Dynamic Management Views and Functions (Transact-SQL)at:[url]http://technet.microsoft.com/en-us/library/ms187974.aspx[/url]This code works in 2005, but I have not tested in 2008 ... give it a try if it does you have some of what you asked for. If it does / does not work, please post and then my knowledge will increase.[code="sql"]SELECT 'Index Name' = i.name, OBJECT_NAME(i.id) AS 'Table Name', Case Indid WHEN 1 THEN 'Clustered' ELSE 'NonClustered' End 'Type', 'Last Updated' = STATS_DATE(i.id, i.indid),rowmodctr AS '# Rows inserted deleted or updated', --, o.type i.keys FROM sysobjects o, sysindexes i WHERE o.id = i.id AND (o.type <> 'S' AND indid <> 0 AND indid <> 255) Gets date of last statistics update number of rows added, deleted or updated since last update [/code]Same caveat as above, but this will also report on Auto Created Statistic[code="sql"]SELECT o.name AS Table_Name,i.name AS Index_Name,STATS_DATE(o.id,i.indid) AS Date_UpdatedFROM sysobjects o JOIN sysindexes i ON i.id = o.idWHERE xtype = 'U' AND i.name IS NOT NULLORDER BY o.name ASC,i.name ASC[/code]Again tested on 2005 needs to be tested on 2008. Again asking you to report back if it does / does not work in 2008This will tell you the date and a lot more than what you would want to know about indexes.[code="sql"] SELECT o.name as 'Table', i.name as 'Index', c.name as 'Column', 'Index Type' = CASE WHEN PATINDEX('%_wa_sys_%',i.name) = 0 THEN 'Index' ELSE 'Statisical' END, 'Primary' = CASE WHEN (i.status & 0x800)= 0 THEN 'No' ELSE 'Yes' END, 'Clustered' = CASE WHEN (i.status & 0x10)= 0 THEN 'No' ELSE 'Yes' END, 'Unique' = CASE WHEN (i.status & 0x2) = 0 THEN 'No' ELSE 'Yes' END, 'Ignore Dup Key' = CASE WHEN (i.status & 0x1) = 0 THEN 'No' ELSE 'Yes' END, 'Ignore Dup Row' = CASE WHEN (i.status & 0x4)= 0 THEN 'No' ELSE 'Yes' END, 'No Recompute' = CASE WHEN (i.status & 0x1000000) = 0 THEN 'No' ELSE 'Yes' END, 'Computed' = CASE WHEN (c.iscomputed) = 0 THEN 'No' ELSE 'Yes' END, 'Nullable' = CASE WHEN (c.isnullable) = 0 THEN 'No' ELSE 'Yes' END, i.OrigFillFactor AS 'Orig Fill Factor', i.rowcnt as 'Est.RowCount', i.reserved * cast(8 as bigint) as ReservedKB, i.used * cast(8 as bigint) as UsedKB, t.name as 'Column Type', 'Precision' = Case c.xprec WHEN 0 THEN ' ' ELSE CAST(c.xprec as VARCHAR(3)) END, 'Scale' = Case c.xscale WHEN 0 THEN ' ' ELSE CAST(c.xscale as VARCHAR(3)) END, c.Length as 'Length', 'Updated' = STATS_DATE(i.id,i.indid) from sysobjects o with(nolock) inner join sysindexes i with(nolock) on o.id = i.id inner join sysindexkeys k with(nolock) on i.id = k.id and I.indid = K.indid inner join syscolumns c with(nolock) on k.id = c.id and K.colid = c.colid inner join systypes t with(nolock) on c.xtype = t.xtype where o.xtype <> 'S' -- Ignore system objects Order By o.name, i.name [/code]Tue, 16 Mar 2010 17:24:18 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic884125-391-1.aspxYou can view when the stats were last updated for a particular set of stats by expanding the database in SSMS, expand the tables folder, then the statistics folder, and then right click on the set of stats and select properties.Tue, 16 Mar 2010 16:38:09 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic884125-391-1.aspxHi,Is there any way I could get the information like when the reindexing and update statistics was done on a particular database?RegardsSandhyaTue, 16 Mar 2010 12:58:59 GMTSandhya-371593