December 17, 2013 at 2:33 am
CREATE TABLE #Outdated_statistics
([Table name] sysname,
[Index name] sysname,
[Last updated] datetime NULL,
[Rows modified] int NULL)
--Insert stats details with rows modified since last update
INSERT INTO #Outdated_statistics
SELECT QUOTENAME (OBJECT_NAME(id)), QUOTENAME (name),STATS_DATE(id, indid),rowmodctr
FROM sys.sysindexes
WHERE STATS_DATE(id, indid)<=DATEADD(DAY,-1,GETDATE())
AND rowmodctr>0
AND id IN (SELECT object_id FROM sys.indexes)
Whats the easiest way of adding the schema to the above code? This is only the first part.. the second part selects the out of date stats through a cursor and updates them as and where needed.
I know all the objects are in sys.objects which has the schema_id column and this can be linked up the the schema_ID on sys.shemas along with the name.. but I have no idea how to incorporate this?? ANy assistance would be appreciated. The code works fine until you use any other schema than DBO
December 17, 2013 at 2:44 am
Something like this:
SELECT QUOTENAME (OBJECT_NAME(i.id)), QUOTENAME (i.name),STATS_DATE(i.id, indid),rowmodctr,so.schema_id,SCHEMA_NAME (so.schema_id) Schemaname
FROM sys.sysindexes i
inner join sys.objects so on i.id = so.object_id
WHERE STATS_DATE(id, indid)<=DATEADD(DAY,-1,GETDATE())
AND rowmodctr>0
AND id IN (SELECT object_id FROM sys.indexes)
and so.type = 'U'
Also, I've excluded system objects
December 17, 2013 at 3:28 am
thanks for that! Worked a treat
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply