SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reindex and Update Statistics History


Reindex and Update Statistics History

Author
Message
crazy4sql
crazy4sql
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4307 Visits: 4514
just for curosity...
what about 2000? how to check the update?

----------
Ashish
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16025 Visits: 25280
This code works in SQL 2000
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



This works in SQL 2000

SELECT o.name AS Table_Name,i.name AS Index_Name,STATS_DATE(o.id,i.indid) AS Date_Updated
FROM sysobjects o
JOIN sysindexes i ON i.id = o.id
WHERE xtype = 'U' AND i.name IS NOT NULL
ORDER BY o.name ASC,i.name ASC



And this works in SQL 2000 - (All tested using Query Analyser - Northwind DB

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



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36154 Visits: 11361
Very nice Ron!

Can't believe anyone is still using 2000 though ;-) :-P

(Please no-one take that statement seriously - thanks)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
crazy4sql
crazy4sql
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4307 Visits: 4514
Thanks 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:-D

----------
Ashish
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search