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.
asked 08 Apr '11, 13:50
With the procedure as-is, you could gather the results needed with a lateral table: