Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Reindex and Update Statistics History Expand / Collapse
Author
Message
Posted Friday, March 19, 2010 11:46 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 17, 2014 2:34 AM
Points: 882, Visits: 4,125
just for curosity...
what about 2000? how to check the update?


----------
Ashish
Post #886611
Posted Friday, March 19, 2010 4:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #886774
Posted Friday, March 19, 2010 9:40 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:53 PM
Points: 9,928, Visits: 11,194
Very nice Ron!

Can't believe anyone is still using 2000 though

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




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #886837
Posted Saturday, March 20, 2010 3:34 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 17, 2014 2:34 AM
Points: 882, Visits: 4,125
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


----------
Ashish
Post #886869
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse