Customize the default template for stored procedure?

  • Back in SQL2000 you could open the default template for an SP-- alter it, and do a special save (save as?) to store it back as your NEW default template.

    I've managed to do similar for SQL 2005 but my memory and notes have failed me. I know about View/Template Explorer; I drill down to Stored Procedure/Create Procedure (New Menu).SQL-- which I may have created in the first place-- and I see the template I get when I create a new SP. This file is on my client machine at C:\Documents and Settings\(MYLOGINNAME)\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql\Stored Procedure.

    I can alter it-- save it-- and if I open it again from Template Explorer, YES I do see my change. But if I right click within a db on Stored Procedures/New Stored Procedure-- I see it without the change I just made. I've restarted SSMS but no joy. The top of the template even says

    -- Template generated from Template Explorer using:

    -- Create Procedure (New Menu).SQL

    I know there's something that will be incredibly obvious once you enlighten me... what did I forget?

    EXTRA CREDIT QUESTION:

    In both 2000 and 2005, this template only applies/applied to the client machine on which I've created the special template. If I want others to use it I'd have to send the file and dirrections to get them using a customized template. This would be RealHandyTM if it could be set at the server level... Does anyone know if there's a way to make it global-- so all users will get this template when they create a new SP?


    Cursors are useful if you don't know SQL

  • The easiest way is in Management Studio, to select View > Template Explorer from the menu bar. (or press Ctrl + Alt + T) From there, pick the template you want from the tree, and right click on it and select Edit. When you make changes and save, it saves those changes to the template directly.

    I don't know of a way to make it "global" for all developers though. There doesn't seem to be anyting in Options for directory of the templates.

  • That's what I've done but it does not work. After altering the template that's shown in "Template Explorer" it does not become the new default template. When I right click on "Stored Procs" in a db and click "New SP" I do not get the newly altered version of the template. Sure-- if I open it from TE I get the altered version, but that's not behaving as a default at that point.

    I don't know of a way to make it "global" for all developers though. There doesn't seem to be anyting in Options for directory of the templates.

    I didn't know if such an option existed... it was a hope though.


    Cursors are useful if you don't know SQL

  • OK, I guess I misunderstood. I see now what you are saying, you're right, even if I edit the one named Create Stored Procedure (New Menu) it doesn't use that one from Object Explorer. (although it looks just like the original template by that name)

    I've aways created a new script from a template by double-clicking the template from the template explorer, and then use Query > Specify Values for Template Parameters. I typically don't do the right click, New Stored Procedure from within the Object Explorer.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply