Notes On Accumulation
When you accumulate results, there are a few guidelines you should follow.
Ensure your first result set allows room for subsequent result sets.
flag for SP_map
is quite useful—particularly for queries that return many small result sets. You do need to be cognizant, however, that SP_map
may not be able to do what you want without some additional guidance. Take, for example, this conventional query to generate a list of all table names:
EXEC sp_msForEachTable " select '?' "
(Yes, it would be much more direct to just use SELECT name FROM sysobjects WHERE xtype = 'U'
to generate a list of table names in a single result set, but this serves as a simple example for discussion.)
That query generates many result sets. You will probably find yourself wanting to feed any
query that makes use of sp_msForEachTable
just to have your results reduced to a convenient, single result set. The first step needed, as already discussed, is to name the anonymous column:
EXEC sp_msForEachTable " select '?' as TableName"
Next, you need to put that query into a string, so you must double the quotes to retain them:
@StmtTemplate = ' EXEC sp_msForEachTable "
select ''?'' as TableName" '
Now, here's the subtle point that might leave you perplexed without this exposition: If you just pass that to SP_map
. . .
@StmtTemplate = ' EXEC sp_msForEachTable
" select ''?'' as TableName" ',
@Accumulate = 1
. . . you get an error similar to this—unless you are very lucky:
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
Why? Recall that the accumulation is done by creating a temporary table on the fly, whose definition is inferred from the first result set. Assume the result set contains a string. Unless that result set just happens to have the longest string in that field of any
value from all
the following results sets, the subsequent result sets attempt to put a string that is too long into the field, yielding the above error. You must provide explicit guidance to tell SP_map
that the name field should have room for a longer string, irrespective of how short or how long the value in the first result set is. Here is one possible solution; if your tables have longer names, use a larger conversion value:
Ensure all your result sets have the same shape.
'EXEC sp_msForEachTable "select convert(
varchar(128), ''?'') as Name" ',
@Accumulate = 1
Using an @StmtTemplate
such as SELECT * FROM <MAIN_ARG>
will work only if each table in your argument list has the same number of columns and the datatypes match. In other words, you cannot accumulate a result set containing five columns with a second result set containing three columns. Again, accumulation works by dynamically generating a temporary table from the inferred structure of the first result set. Each subsequent result set is merged into the temporary table—so each column must be compatible with the temporary table structure.
@AccumulateFilter to filter the accumulated result set.
Here's one final tip to help you leverage the power of SP_map
together with sp_msForEachTable
. You can use sp_msForEachTable
to set your universe of discourse to all tables, then use SP_map
to filter this set down to just those elements you are interested in. Doing this without SP_map
gives you a collection of empty result sets interspersed with some populated ones, as with this query to list all the tables containing "Vendor" as part of the table name. Using the AdventureWorks database, this returns 72 result sets where 68 of them are empty and the remaining 4 each contain a single row:
"select convert(varchar(128), '?') as Name
where '?' like '%Vendor%' "
Simply wrapping that with SP_map
and enabling @Accumulate
automatically filters out any empty result sets. Using the AdventureWorks database, this returns a single result set with 4 rows:
"select convert(varchar(128), ''?'') as Name
where ''?'' like ''%Vendor%'' " ',
@Accumulate = 1
That is great when feeding a SELECT
statement to sp_msForEachTable
, but what if you want to feed it a stored procedure? Recall that when you enable @Accumulate
collects its result sets into a temporary table and then emits the contents of that populated table with a simple SELECT
statement at the end:
SELECT * FROM #Map_Temp_Table
You may add a filtering predicate to this statement via the @AccumulateFilter
parameter. Returning to the example with sp_spaceused
, this invocation displays only the non-empty tables:
@StmtTemplate = "execute sp_spaceused <MAIN_ARG>",
@MainArray = 'Users, Permissions',
@Accumulate = 1,
@AccumulateFilter = "rows > 0"