Performance Analysis Script

[skasarla]

Sorry for the typo if it happended while error checking. I appreciate your input and your point is well taken. To clarify this SP is intended to provide Table name, all the Indexes which have been updated since the last stats/index update. The corrected script is pasted here under. I will work with DB Journal staff to fix the same in the "Scripts" section. I am using this in production and as I use this for generating script of the "tables" to be updated. That comes out accurate with parameter option "A".

****************************************
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE sp_dba_ShowMe_TableStats @dbname sysname=NULL, @option char(1) = NULL
AS
--- Author: Sravan Kasarla
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 [table name], substring(i.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 '-------------------------------------------------------------------------------------------------------------------------------------'
End
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

***************************************