Map

Map @StmtTemplate, @MainArray, @AuxArray, @Permute, @Accumulate, @AccumulateFilter, @Verbose, @Process

where:
   @StmtTemplate      ::= [DML statement]
   @MainArray         ::= "'" { [main item] "," } [main item] "'" | [select statement]
   @AuxArray          ::= "'" { [aux item]  "," } [aux item]  "'" | [select statement]
   @Permute           ::= "1" to perform all permutations, or "0" to match indices
   @Accumulate        ::= "1" for single result set; "0" for individual result sets
   @AccumulateFilter  ::= [filtering predicate]
   @Verbose           ::= "0"-"3" for no diagnostic output or progressively more
   @Process           ::= "1" to execute; "0" to only show generated statements

@StmtTemplate [ Type: nvarchar(2048) Default: null ]

A DML statement (select, insert, dbcc, etc.) that you wish to map to a set of tables or other values. @StmtTemplate should contain the literals '<MAIN_ARG>' and '<AUX_ARG>' as placeholders for @MainArray or @AuxArray elements, respectively.

@MainArray [ Type: nvarchar(2048) Default: null ]

@MainArray may be either a comma-separated list of constants wrapped as a single string, or a SELECT statement that returns a single-column result. In either case, an individual item may contain up to 128 characters.

@AuxArray [ Type: nvarchar(2048) Default: null ]

@AuxArray has the same format as @MainArray: it may be either a comma-separated list of constants wrapped in a string, or a SELECT statement that returns a single-column result. If @AuxArray is null, the <AUX_ARG> placeholder should not be present in the @StmtTemplate.

@Permute [ Type: bit Default: 0 ]

If turned on, then all combinations of MAIN_ARG and AUX_ARG are evaluated. If turned off, then @MainArray and @AuxArray are referenced as pair-wise lists and must therefore have the same cardinality.

@Accumulate [ Type: bit Default: 0 ]

For queries that return result sets, Map by default returns a separate result set for each element. Setting the @Accumulate flag instead aggregates the results into a single result set.

@AccumulateFilter [ Type: nvarchar(2048) Default: null ]

When you accumulate result sets into a single result set, that result set is stored in a temporary table. The generated sequence of code ends with a simple "SELECT * FROM #temp_taple". To filter the results specify a predicate in @AccumulateFilter, which changes the statement to "SELECT * FROM #temp_taple WHERE (@AccumulateFilter)".

@Verbose [ Type: smallint Default: 0 ]

For diagnostic purposes, you may display the dynamic SQL generated to process your request. Each level is cumulative, producing more (rather than alternate) output.

• 0 = No output.
• 1 = Statements with MAIN_ARG replaced (and AUX_ARG if not permuting).
• 2 = Templates; statement permutations with MAIN_ARG and AUX_ARG replaced.
• 3 = Completed statement batch ready to execute.

@Process [ Type: bit Default: 1 ]

To see the generated SQL without executing it, set @Process to 0. To be useful, however, you must also set @Verbose to a non-zero value, otherwise no output is displayed and nothing is executed!

Map is particularly handy for reducing multiple result sets into a single result set, whether the source is a series of queries with similarly shaped result sets or a single super query (a la sp_msForEachTable).

Installation

Enable ad hoc distributed queries (Sql Server 2005)

Run the SQL Server 2005 Surface Area Configuration tool. Select the "Surface Area Configuration for Features" option, which gives you access to a list of special features (see Figure 9). Select Ad Hoc Remote Queries in the navigation tree on the left, and check the "Enable OPENROWSET and OPENDATASOURCE support" on the right, as shown. Select OK, and then close the surface area configuration tool. You do not have to restart SQL Server; the change takes effect immediately.

Determine the server and instance name of your database and, if necessary, update the Map source code.

Map assumes that the standard (local) notation connects to your database server, but SQL Server 2005 Express users may need to adjust this. The installation wizard of SQL Server 2005 Express lets you create a default instance or a named instance of SQL Server Express. This Instance Name page of the Books Online reference states: "SQL Server Express always installs a named instance (SQLExpress) unless you select a default instance. This behavior differs from SQL Server 2005, which installs a default instance unless a named instance is selected." If you selected the default instance when you installed the DB, then (local) correctly specifies your server. However, if you installed as a named instance, you need to change the @SERVER definition in Map.sql to (local)\SQLEXPRESS.

Install into your database

Run Map.sql to install the Map procedure, and run list2table.sql to install the support functions Map uses.

Notes on Accumulation

When you activate the @Accumulate flag keep in mind these tips.

Name any Anonymous Columns

Any computed or anonymous columns must be named. For example, change

    SELECT count(*) from <MAIN_ARG>

to

    SELECT count(*) as Count from <MAIN_ARG>

Double any embedded single quotes

Since you will be passing your query as a string constant inside single quotes, to retain any embedded single quotes you must double them.

Ensure your first result set allows room for subsequent result sets

As an example, take this conventional query to generate a list of all table names (there are better ways to do this, but it is a useful example here):

    EXEC sp_msForEachTable " select '?' as Name"

That query generates many result sets. When accumulating, all the result sets are stored in a temporary table created on the fly, whose definition is inferred from the first result set. Unless that initial 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 an error. You must provide explicit guidance to tell 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, as in:

    EXEC sp_msForEachTable "select convert(varchar(128), '?') as Name"

Ensure all your result sets have the same shape

Using a @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 data types 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.

Use @AccumulateFilter to filter the accumulated result set

Map can be used to filter output from sp_msForEachTable down to just those elements you are interested in. Without Map you get 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:

    EXEC sp_msForEachTable "select convert(varchar(128), '?') as Name where '?' like '%Vendor%' "

Simply wrapping that with Map and enabling @Accumulate automatically filters out any empty result sets. Using the AdventureWorks database, this returns a single result set with 4 rows:

    EXEC Map
        @StmtTemplate =
            'EXEC sp_msForEachTable "select convert(varchar(128), ''?'') as Name
                where ''?'' like ''%Vendor%'' " ',
        @Accumulate   = 1

To use a stored procedure instead of a SELECT statement, you need to do it a bit differently. Recall that when you enable @Accumulate, Map 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, e.g. SELECT * FROM #Map_Temp_Table You may add a filtering predicate to this statement via the @AccumulateFilter parameter. As an example, this invocation displays only the non-empty tables returned from sp_spaceused:

    EXEC Map
        @StmtTemplate = "execute sp_spaceused <MAIN_ARG>",
        @MainArray    = 'Users, Permissions',
        @Accumulate   = 1,
        @AccumulateFilter = "rows > 0"

Other Tips

Customization

If for some reason you wish to use a different place holder (whether you do not like angle brackets or do not like the term MAIN_ARG just change the definition of @MAIN_TOKEN.
do a global substitution for 128 in Map.sql to whatever size you need.

Dynamically generate your own SQL statements

Using Map the normal way lets you specify a template with one or two placeholders that you fill in from lists. If you "invert" Map you can even use unlimited place holders. Say, for example, you want to generate a series of CREATE statements to generate indexes based on existing database structures. Here is an interesting query I found (see here) to generate the code to recreate all indexes of a database:

    SELECT 'CREATE ' + CASE IS_UNIQUE WHEN 1 THEN ' UNIQUE' ELSE '' END
        + CASE IS_CLUSTERED WHEN 1 THEN ' CLUSTERED' ELSE '' END
        + ' INDEX [' + INDEX_NAME + '] ON [dbo].[' + TABLE_NAME + ']'
        + ' (' + COLUMN_LIST + ') ON [' + FILE_GROUP + '];' from vAllIndexes

That statement uses 6 fields to build the query. To use it with Map, that statement becomes the @MainArray parameter instead of the @StmtTemplate parameter. Then just set the @StmtTemplate to <MAIN_ARG> as in:

    EXEC Map
        @StmtTemplate = '<MAIN_ARG>',
        @MainArray='SELECT ''CREATE ''
            + CASE IS_UNIQUE WHEN 1 THEN '' UNIQUE'' ELSE '''' END
            + CASE IS_CLUSTERED WHEN 1 THEN '' CLUSTERED'' ELSE '''' END
            + '' INDEX ['' + INDEX_NAME + ''] ON [dbo].['' + TABLE_NAME + '']''
            + '' ('' + COLUMN_LIST + '') ON ['' + FILE_GROUP + ''];'' from vAllIndexes'