How to manage SQL source code?

Question

I am responsible for the database. It has about 126 sprocs, about 20 views, some UDF. There are several tables that store fixed configuration data for our various applications.

I am using a text file with one large text containing IF EXIST ... DELETE GO CREATE PROCEDURE ... for all sprocs, udfs, views and all attachments / updates for configuration scripts.

Over time, new sprocs have been added or existing sprocs have been modified.

The biggest mistake (as far as I know) that I created when creating this BIG text file was to use the code for the new / changed sprocs at the beginning of the text file. However, I forgot to exclude the previous code for new / changed sprocs. Let's illustrate this:

Say my BIG script (version 1) contains a script to create sprocs

sp 1 sp 2 sp 3 view 1 view 2 

The database version table is updated with version 1.

Now there are a few changes in sp 2. So, now version 2 of the BIG script:

 sp2 --> (newly added) sp1 sp2 sp3 view 1 view 2 

So, obviously, running a BIG script version 2 will not update my sp 2.

I have long understood this with a 100+ number of sprocs.

Corrective Action:

• I created a folder structure. One subfolder for each sproc / view.

• I went through the latest BIG script from bgeinning and put the code for all the scripts in the appropriate folders. Some scripts are repeated several times in a BIG script. If there is more to create a specific sproc than on a block of code, I put the earlier version in another subfolder called "old" in the folder for that sproc. Fortunately, I always documented all the changes I made to all sprocs / view, etc. - I write down the date, version number and description of the changes made as a comment in the sproc code. This helped me figure out the latest version of code for sprocs when there are more than one block of code for sproc.

• I created a DOS batch process to combine all the individual scripts to create my BIG script. I tried using .net streamreader / writer, which messed up the encoding and the "£" sign. Thus, I stick to the DOS batch for a while.

Is there a way to improve the whole process? Right now, I am somehow documenting a version of a BIG script along with its individual versions of sproc. For example, I like to have a way to document

 Big Script (version 1) contains sp 1 version 1 sp 2 version 1 sp 3 version 3 view 1 version 1 view version 1 Big script (version 2) has sp 1 version 1 sp 2 version 2 sp 3 version 3 view 1 version 1 view 2 version 1 

Any feedback is appreciated.

Answer 1

Have you looked at the Visual Studio Team System Database Database Edition (now folded into the Developer Edition)?

One of the things he will do is to support SQL to build the entire database, and then apply only the changes to update the target to a new state. I believe that a script will also be created using the reference database to bring the database matching the reference scheme to the current model (for example, to deploy to production without developers having access to the production).

Answer 2

We do this to have separate files for tables, stored procedures, views, etc. and store them in your own directories. To execute, we simply have a script that executes all the files. Its definitely a lot easier to read than having one huge file.

To update each table, for example, we use this template:

 if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) begin CREATE TABLE [dbo].[MyTable]( [ID] [int] NOT NULL , [Name] [varchar](255) NULL ) ON [PRIMARY] end else begin -- MyTable.Name IF (SELECT COL_LENGTH('MyTable','Name')) IS NULL BEGIN ALTER TABLE MyTable ADD [Name] [varchar](255) NULL PRINT 'MyTable.Name CREATED.' END --etc end 

Answer 3

When I had to process several SQL tables, procedures, and triggers, I did the following:

• All files are under version control (CVS at the time, but look at SVN or Bazaar).
• One file per object named after the object
• makefile indicating dependencies between files

It was an oracle project, and every time you change a table, you have to re-arrange its triggers. And my triggers used several modules, so they had to be recompiled when updating their dependent modules ...

The makefile avoids the “big file” approach: you do not need to execute ALL code for every change.

In windows you can download "NMAKE.exe" to use make files

NTN

Answer 4

Please see my answer to a similar question that might help:

Database Schema Updates

Some additional points:

When we make a release, for example, for Version 2, we merge all Sprocs that have a modified date that is later than the previous version.

We try to add at least one empty line to the bottom of each Sproc script and run each Sproc script with a comment - otherwise concatenation can give "GOCREATE NextSproc" - this is a bore!

When we run a concatenated script, we sometimes find that we get conflicts - for example. calling sub-sprocs that don't exist yet. We duplicate the code for such Sprocs at the bottom of the script, so they are re-created a second time to make sure the SQL Server dependency table is correct. (i.e. we sort it in QA stage for release)

In addition, we put the GRANT permission statement at the bottom of each Sproc script, so when we Drop / Create SProc, we re-grant the permissions. However, if your permissions are assigned to each user or are assigned differently on each server, then it is better to use ALTER rather than CREATE, but this is a problem if SProc does not exist yet, so the best thing to do is:

 IF NOT EXIST ... CREATE PROCEDURE MySproc AS SELECT 'STUB' GRANT EXECUTE Permissions 

and then that Stub is immediately replaced by the real expression ALTER Sproc.