Save SQL Object in Subversion Repository or on Disk

I like to look back at this Time when i was working on that small Company. We had not that much Budget to use a common Sourcecontrol Product like Source Safe or Clear Case to storing our Development Files (Active Server Pages and Sql Object). We also was working acros the hole World with alot of Mobility. My Notebook was always on my side, in the Office, at home and either on tour in the train. Development should be possible everywhere.

---

We Desided to use Subversion together with Trac. The whole ASP Projectfile was always on Eye, but how we could Track Databasechanges accross all our Clients?

We Desided to use Subversion together with Trac. But we had to Script the Objects from SQL Server in a File.

For this approach we created tree Files:

• usp_ScriptObjects.sql (Can Script one Object)
• usp_ScriptAllObjects.sql (get Object Names from Database and execute the usp_ScriptObjects.sql with all required Parameters)
• ScriptDatabase.sql (chosse the Path where the script should be stored)

Maybe there are other users they whould script the Database. But for us it was a confortable way. Just execute this script and Checkin to Subversion.

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    CREATE      PROCEDURE usp_ScriptObjects
        @ServerName varchar(30),
        @DBName varchar(30),
        @ObjectName varchar(100),
        @ObjectType nvarchar(100),
        @TableName nvarchar(100),
        @ScriptFile varchar(255)
    AS
    SET LANGUAGE deutsch

    DECLARE @CmdStr varchar(255)
    DECLARE @object int
    DECLARE @hr int

    SET NOCOUNT ON
    SET @CmdStr = 'Connect('+@ServerName+')'
    EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT

    --Comment out for standard login
    EXEC @hr = sp_OASetProperty @object, 'LoginSecure', TRUE

    -- Codepage...
    EXEC @hr = sp_OAMethod @object, 'SetCodePage(0)'

    /* Uncomment for Standard Login
    EXEC @hr = sp_OASetProperty @object, 'Login', 'sa'
    EXEC @hr = sp_OASetProperty @object, 'password', 'sapassword'
    */

    EXEC @hr = sp_OAMethod @object,@CmdStr
    SET @CmdStr =
      CASE @ObjectType
        WHEN 'Database'     THEN 'Databases("'
        WHEN 'Procedure'    THEN 'Databases("' + @DBName + '").StoredProcedures("'
        WHEN 'View'     THEN 'Databases("' + @DBName + '").Views("'
        WHEN 'Table'    THEN 'Databases("' + @DBName + '").Tables("'
        WHEN 'Index'    THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Indexes("'
        WHEN 'Trigger'  THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Triggers("'
        WHEN 'Key'  THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Keys("'
        WHEN 'Check'    THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Checks("'
        WHEN 'Job'  THEN 'Jobserver.Jobs("'
      END

    if @ObjectType = 'Table'
    BEGIN
        SET @CmdStr = @CmdStr + @ObjectName + '").Script(5,"' + @ScriptFile + '")'
    END
    ELSE
    BEGIN
        -- Write Files in Unicode
        SET @CmdStr = @CmdStr + @ObjectName + '").Script(5,"' + @ScriptFile + '", 4)'
    END
    print @CmdStr
    EXEC @hr = sp_OAMethod @object, @CmdStr
    EXEC @hr = sp_OADestroy @object

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    CREATE       PROCEDURE usp_ScriptAllObjects
        @ServerName varchar(30),
        @DBName varchar(30),
        @ObjectType varchar(10) = 'Procedure',
        @Path varchar(255) = 'c:\Temp\'
    AS
    SET LANGUAGE deutsch


        DECLARE @Name as varchar(60)
        DECLARE @ObjectName as varchar(100)
        DECLARE @Filename as varchar(255)

        DECLARE @FilterParameter as char(2)

        --Means this is Procedure, View and Table
        DECLARE @isSimpleGenerate as bit

        -- Means this is Trigger or Userdefined Function or Index
        DECLARE @isComplexGenerate as bit

        DECLARE @stmt nvarchar(4000)

        SET @FilterParameter =
          CASE @ObjectType
            --WHEN 'Database'   THEN
            --usp_ScriptObjects

            WHEN 'Procedure'    THEN
            'P'

            WHEN 'View'     THEN
            'V'

            WHEN 'Table'    THEN
            'U'

            WHEN 'Index'    THEN
            'I'

            WHEN 'Trigger'  THEN
            'TR'

    --      WHEN 'Check'    THEN

    --      WHEN 'Job'      THEN
          END
        DECLARE @isIndex as bit


        if @FilterParameter = 'U' or @FilterParameter = 'P' or @FilterParameter  = 'V'
        BEGIN
            select @isSimpleGenerate = 1
        END

        if @FilterParameter = 'I' or @FilterParameter = 'TR' or @FilterParameter = 'UF'
        BEGIN
            select @isComplexGenerate = 1
        END

        if @isSimpleGenerate = 1
        BEGIN

            DECLARE c_Objects cursor for
            select name from dbo.sysobjects where type = @FilterParameter and not left(name, 3)='dt_'
            open c_Objects
            FETCH NEXT FROM c_Objects into @Name

            while @@fetch_status = 0
            begin
                FETCH NEXT FROM c_Objects into @Name
                select @Filename = @Path + @Name + '.sql'

                exec usp_ScriptObjects
                    @ServerName = @ServerName,
                    @DBName = @DBName,
                    @ObjectName = @Name,
                    @ObjectType = @ObjectType,
                    @ScriptFile = @Filename
            end

            close c_Objects
            DEALLOCATE c_Objects

        END

        if @isComplexGenerate = 1
        BEGIN
            create table #objsearch(
            ObjectName  sysname COLLATE database_default not null,
            ObjectType  sysname COLLATE database_default not null,
            TableName   sysname COLLATE database_default not null)

            if @FilterParameter = 'I'
            begin
                select @stmt = 'insert into #objsearch select i.name as ObjectName, ObjectType = ''I'', o.name as TableName from dbo.sysindexes i, dbo.sysobjects o where i.id = o.id and (i.indid not in (0, 255)) and (i.status&(32 + 64 + 2048 + 4096) = 0) and xtype = ''u'''
            end
            if @FilterParameter = 'TR'
            BEGIN
                select @stmt = 'insert into #objsearch select o.name as ObjectName, o.xtype as ObjectType, object_name(o.parent_obj) as TableName from dbo.sysobjects o where o.xtype in (''TR'')'
            END

            exec (@stmt)
            if @@error <> 0
            begin
                raiserror ('Error Insert Indexes in Table, Procedure aborted', 16, 1, @dbname)
                return 1
            end


            DECLARE c_Objects cursor for
            select ObjectName, TableName from #objsearch
            open c_Objects
            FETCH NEXT FROM c_Objects into @ObjectName, @Name

            while @@fetch_status = 0
            begin
                FETCH NEXT FROM c_Objects into @ObjectName, @Name
                select @Filename = @Path + @ObjectName + '.sql'

                exec usp_ScriptObjects
                    @ServerName = @ServerName,
                    @DBName = @DBName,
                    @ObjectName = @ObjectName,
                    @ObjectType = @ObjectType,
                    @ScriptFile = @Filename

            end

            close c_Objects
            DEALLOCATE c_Objects
            drop table #objsearch

        END


    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    /***************************************************
    *
    *   Script your Database Objects
    *
    **************************************************/

    DECLARE @ServerName as varchar(30)
    DECLARE @DBName as varchar(30)
    DECLARE @PathProcedure as varchar(255)
    DECLARE @PathTable as varchar(255)
    DECLARE @PathView as varchar(255)
    DECLARE @PathIndex as varchar(255)
    DECLARE @PathTrigger as varchar(255)

    Select @ServerName = 'localhost'
    Select @DBName = DB_Name() --'Northwind'

    Select @PathProcedure = 'c:\Temp\_DB\P\'
    EXECUTE usp_ScriptAllObjects @ServerName,@DBName,'Procedure',@PathProcedure

    Select @PathTable = 'c:\Temp\_DB\U\'
    EXECUTE usp_ScriptAllObjects @ServerName,@DBName,'Table',@PathTable

    Select @PathView = 'c:\Temp\_DB\V\'
    EXECUTE usp_ScriptAllObjects @ServerName,@DBName,'View',@PathView

    Select @PathIndex = 'c:\Temp\_DB\I\'
    EXECUTE usp_ScriptAllObjects @ServerName,@DBName,'Index',@PathIndex

    Select @PathTrigger = 'c:\Temp\_DB\TR\'
    EXECUTE usp_ScriptAllObjects @ServerName,@DBName,'Trigger',@PathTrigger
    GO