I'm adding a maintenance tool to our products to allow users to detect and fix excessive table and index fragmentation using sa_table_fragmentation() and REORGANIZE TABLE. While I can construct my own table list and then call sa_table_fragmentation() for each table, it would seem much more efficient to start off by calling sa_table_fragmentation() with no params to get all tables and then allow users to decide which ones need attention. However because sa_table_fragmentation() does not include the owner name in its result set I call REORGANIZE TABLE without either hoping that there's only table of that name, or doing some further look-ups to see if there are multiple tables, who the owners are and then processing all the tables (since I don't know which of the tables sa_table_fragmentation() is referring to). in this example, two tables called tag, belonging to different owners, but no indication of which is which:
If sa_table_fragmentation() included the owner name (or the TableID), there would be no problem. sa_index_density() includes TableID, so in that case there is no problem identifying which table is meant. |
With the procedure as-is, you could gather the results needed with a lateral table:
Thanks - that's very helpful
(18 Apr '11, 15:35)
Justin Willey
|