How to Manage SQL Source Code?

Question

I am in charge of a database. It has around 126 sprocs, some 20 views, some UDFs. There are some tables that saves fixed configuration data for our various applications.

I have been using a one-big text file that contained IF EXIST ...DELETE GO CREATE PROCEDURE... for all the sprocs, udfs, views and all the insert/updates for the configuration scripts.

In the course of time, new sprocs were added, or existing sprocs were changed.

The biggest mistake (as far as I am aware of) I have made in creating this BIG single text file is to use the code for new/changed sprocs at the beginning of the text file. I, however, I forgot to exclude the previous code for the new/changed sprocs. Lets illustrate this:

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

sp 1
sp 2
sp 3
view 1
view 2

The databse's version table gets updated with the version 1.

Now there is some change in sp 2. So the version 2 of the BIG script is now:

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

So, obviously running the BIG script version 2 will not going to update my sp 2.

I am kind of late of realise this with 100+ numbers of sprocs.

Remedial Action:

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

2. I have gone through the latest version of the BIG script from the bgeinning and placed the code for all scripts into respective folders. Some scripts are repeated more than once in the BIG script. If there are more than on block of code for creating a specific sproc I am putting the earlier version into another subfolder called "old" within the folder for that sproc. Luckily I have always documented all the changes I made to all sprocs/view etc - I write down the date, a version number and description of changes made as comment in the sproc's code. This has helped me a lot to figure out the the latest version of code for a sprocs when there are more than one block of code for the sproc.

3. I have created a DOS batch process to concatenate all the individual scripts to create my BIG script. I have tried using .net streamreader/writer which messes up with the encoding and the "£" sign. So I am sticking to DOS batch for the time being.

Is there any way I can improve the whole process? At the moment I am after some way to document the versioning of the BIG script along with its individual sproc versions. For example, I like to have some 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 welcomed.

Answer 1

The way we do it is to have separate files for tables, stored procedures, views etc and store them in their own directories as well. For execution we just have a script which 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 2

When I had to handle a handful of SQL tables, procedures and triggers I did the following :

• All files under version control (CVS at that time but look at SVN or Bazaar for example)
• One file per object named after the object
• a makefile stating the dependencies between files

It was an oracle project and every time you change a table you have to recomple its triggers. And my trigges used several modules so had to be recompiled also when their dependent modules were updated ...

The makefile avoid the "big file" approach : you don't have to execute ALL your code for every change.

Under windows you can download "NMAKE.exe" to use makefiles

HTH

Answer 3

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

One of things it will do is allow to maintain the SQL to build the whole database, and then apply only the changes to update the target to the new state. I believe that it will also, given a reference database, create a script to bring a database matching the reference schema up to the current model (e.g. to deploy to production without developers having access to production).

Answer 4

Please see my answer to a similar question, which may help:

http://stackoverflow.com/questions/550662/database-schema-updates/550792#550792

Some additional points:

When we make a Release, e.g. for Version 2, we concatenate together all the Sprocs from that have a modified date more recent than the previous Release.

We are careful to add at least one blank line to the bottom of each Sproc script, and to start each Sproc script with a comment - otherwise concatenation can yield "GOCREATE NextSproc" - which is a bore!

When we run the concatenated script we sometimes find that we get conflicts - e.g. calling sub-Sprocs that don't already exist. We duplicate the code for such Sprocs at the bottom of the script - so they are recreated a second time - to ensure that SQL Server's dependency table is correct. (i.e. we sort this out at the QA stage for the Release)

Also, we put a GRANT permissions statement at the bottom of each Sproc script, so that when we Drop / Create an SProc we re-Grant the permissions. However, if your Permissions are allocated to each user, or are differently assigned on each server, then it may be better to use ALTER rather than CREATE - but that is a problem if the SProc does not already exist, so then it is best to do:

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

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