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