I've been meaning to blog about this for a while and a question from a team member prompted this post (thanks Allan!)
SSMS Scripting Options
In SSMS you can script a stored procedure - right-click a stored procedure, hover over Script Stored Procedure As, hover over Create To, and select New Query Editor Window:
Sometimes the script is generated as shown:
The reason? Your scripting options (Tools-->Options-->Scripting in SSMS 2005 [shown below], Tools-->Options-->Sql Server Object Explorer-->Scripting in SSMS 2008 R2) are set to generate IF NOT EXISTS clauses:
Set this to False to generate stored procedure T-SQL that appears as shown here:
What I'd Like
I would like the If Not Exists statement and the T-SQL without the call to sp_execute. Something like:
IF NOT EXISTS (SELECT s.name + '.' + p.name
FROM sys.procedures p
INNER JOIN sys.schemas s ON s.schema_id = p.schema_id
WHERE s.name = 'dbo'
AND p.name = 'uspGetBillOfMaterials')
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
In a recent email exchange with the SSIS Developer Team at Microsoft, I shared a bunch of things I would like in SSIS. Since I was asking for so much I included a request for a pony. Matt Masson (Blog - Twitter) sent me this image in reply:
The clincher in this image is the inset of a horse with the caption "NOT YOURS". I still laugh out loud whenever I look at this!