Facility9

You know what, there’s no magic here. However, it’s kinda magic to me since I didn’t write it and reading other people’s code is something that is almost like magic to me. And, I have to admit that I did not write this code. One of my co-workers, Brent Morrow, came up with this solution. Sadly, Brent does not have a blog. Luckily, I do. So now I will share with you Brent’s solution for keeping track of objects to move from one platform to another.

What’s the goal here? Well, let’s say we don’t have VSTS: Database Developer edition. And let’s further posit that we don’t have access to any of the commercially available schema comparison tools. Apart from pen and paper, how shall we keep track of the changes that need to go into production?

Well, we can use SQL Server to keep track of these changes.

The first thing we need is a table to track this:

CREATE TABLE [Debug].[DBObjects]
    (
      [LocalID] [int] IDENTITY(1, 1)
                      NOT NULL,
      [Schema] [nvarchar](128) NOT NULL,
      [Name] [nvarchar](128) NOT NULL,
      [ObjectType] [varchar](30) NOT NULL,
      [RequestedBy] [varchar](30) NULL,
      [RequestedDate] [datetime] NULL,
      [ProductionReady] [bit] NULL,
      [ProductionDate] [smalldatetime] NULL,
      [InProduction] [bit] NULL,
      [Comments] [varchar](2048) NULL,
      CONSTRAINT [PK_DBObjects] PRIMARY KEY CLUSTERED ( [LocalID] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY] ;

So, here are the rules for the table:

• Schema – this is the object schema. It’s required.
• Name – the name of the object to be scripted. Also required.
• ObjectType – the type of object that we’re scripting. This needs to be one of the following values: STORED PROCEDURE, VIEW, FUNCTION. This is required. Spelling is important.
• RequestedBy – this shows up in the script, but it’s not required.
• RequestedDate – this also shows up in the script, but it’s not required.
• ProductionReady – this is required, it needs to be set to 1.
• ProductionDate – this is optional, it is just the date that something goes/went into production.
• InProduction – One the object has been moved into production, set this to true (1). If you don’t do this, and ProductionReady is set to true (1), then the object will be scripted the next time that everything is run.
• Comments – optional comments, these show up in the script.

So, what is the next step? Well, you need a way to script all of this. Basically, the following script will execute against the DBOjects table and will generate a script for all of the objects in the table with ProductionReady set to true and InProduct set to false. Once the script has been run against the new environment, set InProduction to true (or remove the row) and the object won’t be scripted in subsequent runs. If you have more changes, add a new line with a new comment and new request date. Good times, eh?

So, a big thanks to Brent Morrow for sharing this code with the world. If you have questions, comments, concerns, improvements, or just want to share kudos, put them down in the comments or drop me an email and I’ll make sure that Brent knows about it.

---------------------------------------------------------------
-- DEFINITIONS
---------------------------------------------------------------

CREATE TABLE #EntryHold ( [Text] VARCHAR(4000) )

CREATE TABLE #Entry ( [Text] VARCHAR(4000) )

DECLARE @LocalID INT
DECLARE @Schema VARCHAR(50)
DECLARE @Name VARCHAR(100)
DECLARE @ObjectType VARCHAR(100)
DECLARE @ProductionReady BIT
DECLARE @InProduction BIT
DECLARE @RequestedBy VARCHAR(50)
DECLARE @RequestedDate DATETIME
DECLARE @Comments VARCHAR(2000)
DECLARE @Counter TINYINT
DECLARE @Supported BIT

DECLARE [EntryCursor] SCROLL CURSOR
    FOR SELECT  [LocalID],
                [Schema],
                [Name],
                [ObjectType],
                [ProductionReady],
                [InProduction],
                [RequestedBy],
                [RequestedDate],
                [Comments]
        FROM    [Debug].[DBObjects]
        ORDER BY [LocalID]

---------------------------------------------------------------
-- HEADER
---------------------------------------------------------------
OPEN [EntryCursor]

-- STORED PROCEDURES NOT MOVED
INSERT  INTO #Entry
VALUES  (
          '--- OBJECTS NOT BEING MOVED ---'
        )

SET @Counter = 0
WHILE @Counter < 3
    BEGIN
        FETCH FIRST FROM [EntryCursor] INTO @LocalID, @Schema, @Name,
            @ObjectType, @ProductionReady, @InProduction, @RequestedBy,
            @RequestedDate, @Comments

        WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @Supported = CASE WHEN @ObjectType IN ( 'VIEW',
                                                            'STORED PROCEDURE',
                                                            'FUNCTION' )
                                      THEN 1
                                      ELSE 0
                                 END

                IF ( ( @ObjectType = 'VIEW'
                       AND @Counter = 0
                     )
                     OR ( @ObjectType = 'STORED PROCEDURE'
                          AND @Counter = 1
                        )
                     OR ( @ObjectType NOT IN ( 'STORED PROCEDURE', 'VIEW' )
                          AND @Counter = 2
                        )
                   )
                    AND ( @ProductionReady IS NULL
                          OR @ProductionReady = 0
                        )
                    AND ( @InProduction IS NULL
                          OR @InProduction = 0
                        )
                    BEGIN
                        INSERT  INTO #Entry
                        VALUES  (
                                  '--REQUESTOR: ' + @RequestedBy + SPACE(12 - LEN(@RequestedBy))
                                  + 'DATE: ' + CONVERT(VARCHAR, @RequestedDate, 101)
                                  + ' | ' + @ObjectType + ': ' + @Schema + '.'
                                  + @Name
                                )
                        IF @Supported = 1
                            INSERT  INTO #Entry
                            VALUES  (
                                      '--        REASON NOT MOVED:  Not production ready!'
                                    )
                        ELSE
                            INSERT  INTO #Entry
                            VALUES  (
                                      '--        REASON NOT MOVED:  Not Supported!'
                                    )

                        IF ( @Comments IS NOT NULL )
                            INSERT  INTO #Entry
                            VALUES  (
                                      '--        COMMENTS:  ' + @Comments
                                    )
                    END
                FETCH [EntryCursor] INTO @LocalID, @Schema, @Name, @ObjectType,
                    @ProductionReady, @InProduction, @RequestedBy,
                    @RequestedDate, @Comments
            END
        SET @Counter = @Counter + 1
    END
INSERT  INTO #Entry
VALUES  (
          '---------------------------------------------------------------------------------------------------'
        )

INSERT  INTO #Entry
VALUES  (
          '--- OBJECTS READY TO MOVE ---'
        )

SET @Counter = 0
WHILE @Counter < 3
    BEGIN
        FETCH FIRST FROM [EntryCursor] INTO @LocalID, @Schema, @Name,
            @ObjectType, @ProductionReady, @InProduction, @RequestedBy,
            @RequestedDate, @Comments
        WHILE @@FETCH_STATUS = 0
            BEGIN
                IF ( ( @ObjectType = 'VIEW'
                       AND @Counter = 0
                     )
                     OR ( @ObjectType = 'STORED PROCEDURE'
                          AND @Counter = 1
                        )
                     OR ( @ObjectType NOT IN ( 'STORED PROCEDURE', 'VIEW' )
                          AND @Counter = 2
                        )
                   )
                    AND ( @ProductionReady = 1 )
                    AND ( @InProduction IS NULL
                          OR @InProduction = 0
                        )
                    BEGIN
                        INSERT  INTO #Entry
                        VALUES  (
                                  '--REQUESTOR: ' + @RequestedBy + SPACE(12 - LEN(@RequestedBy))
                                  + 'DATE: ' + CONVERT(VARCHAR, @RequestedDate, 101)
                                  + ' | ' + @ObjectType + ': ' + @Schema + '.'
                                  + @Name
                                )
                        IF ( @Comments IS NOT NULL )
                            INSERT  INTO #Entry
                            VALUES  (
                                      '--        COMMENTS:  ' + @Comments
                                    )
                    END
                FETCH [EntryCursor] INTO @LocalID, @Schema, @Name, @ObjectType,
                    @ProductionReady, @InProduction, @RequestedBy,
                    @RequestedDate, @Comments
            END
        SET @Counter = @Counter + 1
    END

INSERT  INTO #Entry
VALUES  (
          '---------------------------------------------------------------------------------------------------'
        )
INSERT  INTO #Entry
VALUES  (
          '--- OBJECT SCRIPTS ---'
        )

---------------------------------------------------------------
-- SCRIPTS THAT ARE PRODUCTION READY
---------------------------------------------------------------
SET @Counter = 0
WHILE @Counter  5
                            BEGIN
                                INSERT  INTO #Entry
                                        SELECT  *
                                        FROM    #EntryHold
                            END
                        ELSE
                            BEGIN
                                INSERT  INTO #Entry
                                VALUES  (
                                          '--' + @ObjectType + ' [' + @Schema
                                          + '].[' + @Name + '] was not found.'
                                        )
                                INSERT  INTO #Entry
                                VALUES  (
                                          '---------------------------------------------------------------------------------------------------'
                                        )
                            END
                        DELETE  FROM #EntryHold
                    END

                FETCH [EntryCursor] INTO @LocalID, @Schema, @Name, @ObjectType,
                    @ProductionReady, @InProduction, @RequestedBy,
                    @RequestedDate, @Comments
            END
        SET @Counter = @Counter + 1
    END

---------------------------------------------------------------
-- SCRIPTS THAT ARE NOT PRODUCTION READY
---------------------------------------------------------------
CREATE TABLE #CommentEntry ( [Text] VARCHAR(4000) )

SET @Counter = 0
WHILE @Counter < 3
    BEGIN
        FETCH FIRST FROM [EntryCursor] INTO @LocalID, @Schema, @Name,
            @ObjectType, @ProductionReady, @InProduction, @RequestedBy,
            @RequestedDate, @Comments

        WHILE @@FETCH_STATUS = 0
            BEGIN
                IF ( ( @ObjectType = 'VIEW'
                       AND @Counter = 0
                     )
                     OR ( @ObjectType = 'STORED PROCEDURE'
                          AND @Counter = 1
                        )
                     OR ( @ObjectType NOT IN ( 'STORED PROCEDURE', 'VIEW' )
                          AND @Counter = 2
                        )
                   )
                    AND ( @ProductionReady IS NULL
                          OR @ProductionReady = 0
                        )
                    AND ( @InProduction IS NULL
                          OR @InProduction = 0
                        )
                    BEGIN
                        INSERT  INTO #CommentEntry
                                EXECUTE
                                       ( 'sp_helptext @objname = ''' + @Schema
                                         + '.' + @Name + ''''
                                       )
                        INSERT  INTO #CommentEntry
                        VALUES  ( 'GO' )
                        INSERT  INTO #CommentEntry
                        VALUES  (
                                  '---------------------------------------------------------------------------------------------------'
                                )
                    END

                FETCH [EntryCursor] INTO @LocalID, @Schema, @Name, @ObjectType,
                    @ProductionReady, @InProduction, @RequestedBy,
                    @RequestedDate, @Comments
            END
        SET @Counter = @Counter + 1
    END

INSERT  INTO #Entry
        SELECT  '--' + [Text]
        FROM    #CommentEntry

DROP TABLE #CommentEntry

CLOSE [EntryCursor]
DEALLOCATE [EntryCursor]

DROP TABLE #EntryHold

SELECT  *
FROM    #Entry
DROP TABLE #ENTRY