XML FeedsThis post was already published yesterday, someone deleted it by mistake so I had to recreate it...sorry for that (and thanks for google cache :-))
SSMS Tools PACK is an Add-In (Add-On) for Microsoft SQL Server Management Studio and Microsoft SQL Server Management Studio Express, this tool is developed by Mladen Prajdić.
SSMS Tools PACK contains a few upgrades to the IDE that were missing from Management Studio:
- Window Connection Coloring.
- Query Execution History (Soft Source Control) and Current Window History.
- Search Table or Database Data.
- Uppercase/Lowercase keywords and proper case Database Object Names.
- Run one script on multiple databases.
- Copy execution plan bitmaps to clipboard.
- Search Results in Grid Mode and Execution Plans.
- Generate Insert statements for a single table, the whole database or current resultsets in grids.
- Text document Regions and Debug sections.
- Running custom scripts from Object explorer's Context menu.
- CRUD (Create, Read, Update, Delete) stored procedure generation.
- New query template.
Some of these features are now available in SSMS 2008 but the tool is still very useful if you are using SSMS 2008. I am going to focus on the CRUD (Create, Read, Update, Delete) stored procedure generation functionality.
SSMS Tools PACK is available for the following SSMS versions
SQL Server Management Studio 2008
SQL Server Management Studio 2008 Express
SQL Server Management Studio 2005
SQL Server Management Studio 2005 Express
Download SSMS Tools PACK here: http://www.ssmstoolspack.com/Download.aspx
After it is installed create this table
- CREATE TABLE Test (ID INT PRIMARY KEY IDENTITY,
- LastName VARCHAR(40) not null,
- FirstName VARCHAR(40) not null,
- MiddleInitial CHAR(1) null,
- Salutation VARCHAR(10) null,
- InsertedDate DATETIME not null,
- LastUpdatedDate DATETIME not null)
Now from the menu select SSMS Tools-->CRUD Generator-->Options (see pic below)
I like to prefix my procs with pr instead of usp_ (See pic below)
You can customize how the procs will be generated, so for example the INSERT proc template looks like this
- IF OBJECT_ID('[|schema|].[|procName|]') IS NOT NULL
- BEGIN
- DROP PROC [|schema|].[|procName|]
- END
- GO
- /*
- Created by: SQLDenis
- Version: 1.0
- */
- CREATE PROC [|schema|].[|procName|]
- |inputParams|
- AS
- SET NOCOUNT ON
- SET XACT_ABORT ON
- BEGIN TRAN
- INSERT INTO [|schema|].[|tableName|] (|insertColumnList|)
- SELECT |values|
- -- Begin Return Select <- do not remove
- SELECT |selectColumnList|
- FROM [|schema|].[|tableName|]
- WHERE |whereStatement|
- -- End Return Select <- do not remove
- COMMIT
- GO
As you can see I added a comment block there that has my name and the initial version number. Having all your developers use a tool like this is great because you will have all the same looking procs and don't have to worry that some people use @Error or @ErrorCode or @ErrorID. You just modify the template to have the error checking that you have standardized upon and you are done
So now let's see what SSMS Tools PACK generates
Right click on the table you created, select SSMS Tools and then Create CRUD (See image below)
The following code will be generated
- USE [Test];
- GO
- IF OBJECT_ID('[dbo].[prTestSelect]') IS NOT NULL
- BEGIN
- DROP PROC [dbo].[prTestSelect]
- END
- GO
- /*
- Created by: SQLDenis
- Version: 1.0
- */
- CREATE PROC [dbo].[prTestSelect]
- @ID INT
- AS
- SET NOCOUNT ON
- SET XACT_ABORT ON
- BEGIN TRAN
- SELECT [ID], [FirstName], [InsertedDate], [LastName], [LastUpdatedDate], [MiddleInitial], [Salutation]
- FROM [dbo].[Test]
- WHERE ([ID] = @ID OR @ID IS NULL)
- COMMIT
- GO
- IF OBJECT_ID('[dbo].[prTestInsert]') IS NOT NULL
- BEGIN
- DROP PROC [dbo].[prTestInsert]
- END
- GO
- /*
- Created by: SQLDenis
- Version: 1.0
- */
- CREATE PROC [dbo].[prTestInsert]
- @FirstName VARCHAR(40),
- @InsertedDate DATETIME,
- @LastName VARCHAR(40),
- @LastUpdatedDate DATETIME,
- @MiddleInitial CHAR(1),
- @Salutation VARCHAR(10)
- AS
- SET NOCOUNT ON
- SET XACT_ABORT ON
- BEGIN TRAN
- INSERT INTO [dbo].[Test] ([FirstName], [InsertedDate], [LastName], [LastUpdatedDate], [MiddleInitial], [Salutation])
- SELECT @FirstName, @InsertedDate, @LastName, @LastUpdatedDate, @MiddleInitial, @Salutation
- -- Begin Return Select <- do not remove
- SELECT [ID], [FirstName], [InsertedDate], [LastName], [LastUpdatedDate], [MiddleInitial], [Salutation]
- FROM [dbo].[Test]
- WHERE [ID] = SCOPE_IDENTITY()
- -- End Return Select <- do not remove
- COMMIT
- GO
- IF OBJECT_ID('[dbo].[prTestUpdate]') IS NOT NULL
- BEGIN
- DROP PROC [dbo].[prTestUpdate]
- END
- GO
- /*
- Created by: SQLDenis
- Version: 1.0
- */
- CREATE PROC [dbo].[prTestUpdate]
- @ID INT,
- @FirstName VARCHAR(40),
- @InsertedDate DATETIME,
- @LastName VARCHAR(40),
- @LastUpdatedDate DATETIME,
- @MiddleInitial CHAR(1),
- @Salutation VARCHAR(10)
- AS
- SET NOCOUNT ON
- SET XACT_ABORT ON
- BEGIN TRAN
- UPDATE [dbo].[Test]
- SET [FirstName] = @FirstName, [InsertedDate] = @InsertedDate, [LastName] = @LastName, [LastUpdatedDate] = @LastUpdatedDate, [MiddleInitial] = @MiddleInitial, [Salutation] = @Salutation
- WHERE [ID] = @ID
- -- Begin Return Select <- do not remove
- SELECT [ID], [FirstName], [InsertedDate], [LastName], [LastUpdatedDate], [MiddleInitial], [Salutation]
- FROM [dbo].[Test]
- WHERE [ID] = @ID
- -- End Return Select <- do not remove
- COMMIT TRAN
- GO
- IF OBJECT_ID('[dbo].[prTestDelete]') IS NOT NULL
- BEGIN
- DROP PROC [dbo].[prTestDelete]
- END
- GO
- /*
- Created by: SQLDenis
- Version: 1.0
- */
- CREATE PROC [dbo].[prTestDelete]
- @ID INT
- AS
- SET NOCOUNT ON
- SET XACT_ABORT ON
- BEGIN TRAN
- DELETE
- FROM [dbo].[Test]
- WHERE [ID] = @ID
- COMMIT
- GO
- ----------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------
As you can see that is a huge time saver, you can of course customize it so that it conforms to your style guide.
Don't forget to thank Mladen Prajdić on twitter: http://twitter.com/MladenPrajdic or to donate if this tool is useful to you
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
This is sooo awesome, it will save me so much time when creating these CRUD procs since our shop does everything through procedures
Very nicely put together Denis. I'm sure MladenPrajdic would say the same. 
hey this is a nice tool... Few suggestions for your next version :-) On update and delete scripts in addition to primary key, facility to add row version in the where codition to handle optimistic concurrency.
Nice tool - I installed it and recommended to others 
Agree 100%, awesome tool - recently upgraded our servers to SQL 2008 Server, as soon as I installed the developer bits on my PC I downloaded and installed SSMS Tools Pack - I can't use SSMS without it now! 
Many thanks , it's Amazing tool 
How did u show that query window with alternate background colors?....plz tell me... 
Ravie, what window? are you talking about the border? that is just Aero Glass in Windows 7 
Great job. Thank you for your dedication 
Its very nice and handy tool. 
It is one of the best tools.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.