February 6, 2007 at 11:16 am
There is a system stored procedure that I have used before to find this out. However cant seem to find it. Wonder if someone else can help me with that?
thanks
February 6, 2007 at 12:42 pm
Hello,
Can you check this one
DBCC SHOW_STATISTICS
Hope this helps.
Thanks
Lucky
February 6, 2007 at 11:30 pm
This the one you're looking for? Used in a select statement, so you can check the dates of all stats in the system with a single query.
STATS_DATE
( table_id , index_id )
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2007 at 4:32 am
DBCC SHOW_STATISTICS or STATS_DATE
February 7, 2007 at 6:16 am
i had this saved in my snippets catalog: you can use it to determine whether statistics need to be run, or to run them. a paramter of I gets information(default), parameter of A updates statistics.
/*
Run the following script on master database to catalog this stored procedure.
The SP has 2 input paramaters '@dbname' and @option.
@dbname - Name of the database for which you want to update stats
@option - Information - "I" OR Action "A"
Exec sp_dba_ShowMe_TableStats '@dbname','@action'
Example 1: EXEC sp_dba_ShowMe_TableStats 'pubs','I'
Results:
Table Name Index Name Rows Modified
newTitles newTitles 18
EMPSTAT_DIM EMPSTAT_DIM_idx1 1704364
PJR_Sales PJR_Sales 1704364
PJR_Sales PJR_Sales 704364
Exmaple 2: Executing the proc with "A" will fetch the below results
EXEC sp_dba_ShowMe_TableStats 'pubs','A'
Results
---------------------------------
UPDATE STATISTICS EMPSTAT_DIM GO
UPDATE STATISTICS newTitles GO
UPDATE STATISTICS PJR_Sales GO
select o.name,i.name, 'DROP STATISTICS ' + o.name +'.'+i.name as dropstatement
from sysindexes i
inner join sysobjects o ON o.id = i.id
where indexproperty(i.id,i.name,'IsStatistics') = 1
*/
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_NULLS ON
GO
CREATE
PROCEDURE sp_dba_ShowMe_TableStats @dbname sysname=NULL, @option char (1) = 'I'
AS
--- Author: Sravan Kasarla
--- Created: 10/01/2003
BEGIN
Declare
@what char(1),
@qry varchar(2000)
set @what = @option
IF
@what = 'I'
Begin
set @qry = ' Use ' + @dbName +' SELECT substring(o.name,1,50) AS
, substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
ORDER BY i.rowmodctr DESC'
exec (@qry)
End
ELSE
IF @what = 'A'
Begin
Print space(10)+' Run the Update Statistics on the following Tables'
SET @qry = 'SET NOCOUNT ON'+char(13)+ 'Use ' + @dbName + ' SELECT Distinct ''UPDATE STATISTICS''+SPACE(1)+O.NAME+CHAR(13)+''GO'' FROM SYSOBJECTS O
JOIN SYSINDEXES i ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
---ORDER BY O.NAME'
exec (@qry)
End
ELSE
Begin
Print space(10)+'Please pass in the right parameters : DBName and option "I" for Information or "A" Action"'
PRINT '-------------------------------------------------------------------------------------------------------------------------------------'
set @qry = ' Use ' + @dbName +' SELECT substring(o.name,1,50) AS
, substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
ORDER BY i.rowmodctr DESC'
exec (@qry)
End
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET
ANSI_NULLS ON
GO
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply