Scripting Objects with Magic
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] ;
* **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
