One of the projects I’m on has over 2,200 stored procedures. Until I arrived, none of these were under source control — the ‘stable’ version was whatever was on the production database server, and the ‘trunk’ version was whatever was in the development database server (which incidentally wasn’t even backed up).
Anyway, I set about exporting all the stored procedures and checking them into source control. But as easy as it sounds, it actually took a few goes to get the it all right. You see, when generating a CREATE script with IF NOT EXISTS, SQL Management Studio has a habit of generating entire scripts as one giant EXEC sp_executesql string instead of just putting a GO in between. This isn’t very good for development — you don’t get intellisense, syntax highlighting, etc inside a string.
To get around this problem, I ended up generating two separate scripts — first a DROP (with IF NOT EXISTS), then a CREATE (without it) — and set SSMS to put each in the same file (Append to File). This gave me one script per stored procedure, each with an IF EXISTS DROP, GO then CREATE statement — perfect for source control.
Here are some other tips for configuring SQL Management Studio (via Options > SQL Server Object Explorer > Scripting) to make it automatically generate friendly scripts:
- Disable Include descriptive headers. They sound helpful, but all they do is add the script date and repeat the object’s name. This is really annoying because it clutters up my results for source-code text search in Visual Studio.
- Disable Script USE <database>. We often have several copies of the same database running with different names on the same SQL box (e.g. for different development branches). Hard-coding the name of the database in all your scripts means you have to manually change all the USE statements every time you run them. And trust me — the risk of having your changes leak over and get applied to the wrong database is not really something you want to be worrying about.
- Enable Include IF NOT EXISTS clause. Stored procedures, triggers, functions — it’s much easier to just nuke and re-create them every time instead of worrying about the semantics of CREATE vs ALTER. This also means you can just run each script over and over and over again.
If you get it right, SQL Management Studio 2008’s Script DROP and CREATE should produce something that looks like:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Employee_Get]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[usp_Employee_Get]GOCREATE PROCEDURE [dbo].[usp_Employee_Get]AS SELECT EmployeeID, FirstName, LastName FROM EmployeeGO
We also put permissions (e.g. GRANT EXEC) at the end of each script, so they are restored each time the stored procedure is created.
