Well, as Rob pointed out, I'm using SYSTABLESTAT for now but will need
to think about SYSPARTITIONSTAT in the near future.
Anyway, here is what I have so far:
Table_Schema AS Table_Schema
, Table_Name AS Table_Name
, Number_Rows AS Number_Rows
, Number_Deleted_Rows AS Number_Deleted_Rows
WHEN ( Number_Deleted_Rows = 0 ) THEN 0
WHEN ( Number_Rows = 0 ) AND ( Number_Deleted_Rows> 0 ) THEN
ELSE ( 100.00 * Number_Deleted_Rows / ( Number_Rows +
Number_Deleted_Rows ) )
AS DECIMAL( 5 , 2 )
) AS Percent_Deleted
, Data_Size AS Data_Size
, COALESCE( CHAR( DATE( Last_Used_Timestamp ) ), ' ' ) AS Last_Used
, COALESCE( CHAR( DATE( Last_Change_Timestamp ) ), ' ' ) AS Last_Chgd
, COALESCE( CHAR( DATE( Last_Save_Timestamp ) ), ' ' ) AS Last_Saved
, COALESCE( CHAR( DATE( Last_Restore_Timestamp ) ), ' ' ) AS Last_Restored
WHERE Table_Schema = 'MyLIB' -- Use your actual schema name here, etc.
It takes a looooooooong time to run for large schemas, though, so be warned.
"Contrariwise, if it was so, it might be; and if it were so, it would
be; but as it isn't, it ain't. That's logic."--Tweedledee
On 09/17/2013 2:30 PM, Jim Oberholtzer wrote:
> Any chance you could post that to the code scratch pad? I'm trying to
> follow the changes you made and messing it up.
> Jim Oberholtzer
> Chief Technical Architect
> Agile Technology Architects
> On 9/17/2013 7:09 AM, Robert Clay wrote:
>> Thanks, Birgitta!
>> Unless someone else has input, I'll go with this:
This mailing list archive is Copyright 1997-2013 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact