When Does sp_prepare Return Metadata

I was running an RML Utilities Suite test pass and encountered varying behavior from our sp_prepare suite. Here is what I uncovered.

The command sp_prepare returns (or does not return) metadata depending on the server version.  For the client version, it is only significant whether it is prior to SQL 2012 or it is a later one (i.e. 2012 RTM, SP1, etc.).

1. Prior to SQL 2012, sp_prepare returns metadata to the user. This was implemented by internally setting FMTONLY ON and executing the statement.

2. In SQL 2012 RTM and SP1, sp_prepare does NOT return metadata, if client version is 2012 or greater. FMTONLY ON is deprecated and used only for backward compatibility with the older (i.e. 2008) clients.

3. In SQL 2012 CU6 (build 11.0.2401.0) and later, and SP1 CU3 and later, sp_prepare DOES return metadata to the user, if the batch contains one statement.  This is to address a performance issue with some scenarios (see hotfix KB2772525).

The following matrix shows when sp_prepare should return metadata for batches containing one statement.

Client\Server Version	2008/R2	2012 RTM	2012 CU6 +	2012 SP1	2012 SP1 CU3 +	SQL 14
2008 R2	yes	yes	yes	yes	yes	yes
2012 (all versions)	yes	no	yes	no	yes	yes
SQL 14 CTP	yes	no	yes	no	yes	yes

yes - sp_prepare returns metadata
no - sp_prepare does NOT return metadata

The following matrix shows when sp_prepare should return metadata for multi-statement batches, such as

declare @p1 int

set @p1=NULL

exec sp_prepare @p1 output,NULL,N'select * from sys.objects; select 1;',1

select @p1

Client\Server Version	2008/R2	2012 RTM	2012 CU6 +	2012 SP1	2012 SP1 CU3 +	SQL 14
2008 R2	yes	yes	yes	yes	yes	yes
2012 (all versions)	yes	no	no	no	no	no
SQL 14 CTP	yes	no	no	no	no	no

Bob Dorr - Principal SQL Server Escalation Engineer