This was used to solve a specific problem, but some of the techniques here may be of general interest.

A DDL Trigger, operates at the database level, to capture DDL changes.  There are many good articles about using DDL triggers.    I’m not going to repeat the same info here; instead, here is an article that’s pretty good, about DDL Triggers:  https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

So, the scenario is that we have a table where DDL changes are logged, via the DDL Trigger.   The log includes the actual SQL statement that was run at that time.  The changes to the database needed to be scripted out to .sql files.

The solution here, uses SQL’s ability to instantiate COM objects, and the Windows Scripting FileSystemObject COM component.

 

To allow SQL to use COM Objects, we have to use the sp_configure system stored procedure:

--this allows SQL server to instantiate COM objects:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

 

Next, create a stored procedure that will write to file:

--Create the sproc:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[WriteToTextFile]
@File VARCHAR(2000),
@Text VARCHAR(MAX),
@AppendYN CHAR(1) = 'N'

AS 

BEGIN 

DECLARE @OLE INT 
DECLARE @FileID INT 
DECLARE @OverwriteAppendMode INT

IF @AppendYN = 'Y'
 SET @OverwriteAppendMode = 8
ELSE
 SET @OverwriteAppendMode = 2 

EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT 

/*
The sp_OAMethod is executing method of the given object, 
in this case is a FileSystemObject OpenTextFile has 4 parameters in this order:
FilePath -Required. The name of the file to open
Mode - Optional. How to open the file
1 = Reading - Open a file for reading. You cannot write to this file.
2 = Writing - Open a file for writing.
8 = Appending - Open a file and write to the end of the file.
Create - Optional. Sets whether a new file can be created if the filename does not exist. True indicates that a new file can be created, and False indicates that a new file will not be created. False is default.
Format - Optional. The format of the file
0 = TristateFalse - Open the file as ASCII. This is default.
1 = TristateTrue - Open the file as Unicode.
2 = TristateUseDefault - Open the file using the system default.
*/
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @File, @OverwriteAppendMode , 1, 0 

EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, @Text

EXECUTE sp_OADestroy @FileID 
EXECUTE sp_OADestroy @OLE 

END 

 

 

Here is the actual code that writes the changes to files.

DECLARE @objectname varchar(250)
DECLARE @objecttype varchar(100)
DECLARE @db VARCHAR(100)
DECLARE @eventtype VARCHAR(100)
DECLARE @hostPCname VARCHAR(100)
DECLARE @eventdate DATETIME
DECLARE @sqlcommand VARCHAR(MAX)
DECLARE @Path VARCHAR(2000)
DECLARE @Txt VARCHAR(max)
DECLARE @headertext VARCHAR(2000)
DECLARE @appendThis CHAR(1)

DECLARE @c AS CURSOR

SET @c = CURSOR FOR
SELECT databasename, eventtype, objectname, objecttype, sqlcommand, eventdate, HostPCName 
FROM DDLChangeLog
ORDER BY logid

OPEN @c

FETCH NEXT FROM @c INTO @db, @eventtype, @objectname, @objecttype, @sqlcommand, @eventdate, @HostPCName

WHILE @@FETCH_STATUS = 0
BEGIN
-- procs, views and functions, just replace the entire file with new defn
-- other objects, append for ALTERs, replace for CREATEs/DROPs
SELECT @appendThis = CASE WHEN @objecttype IN ('PROCEDURE', 'VIEW', 'FUNCTION') THEN 'N' 
 WHEN @eventtype LIKE 'ALTER%' THEN 'Y' ELSE 'N' END

 SELECT @headertext = N'
 /**********************************************
 Object Name: @objectname
 Database : @db
 Changed On : @eventdate
 Changed By : @hostPCName
 */

 '
 SELECT @headertext = REPLACE(@headertext , '@objectname', @objectname)
 SELECT @headertext = REPLACE(@headertext , '@db', @db)
 SELECT @headertext = REPLACE(@headertext , '@eventdate', CONVERT(VARCHAR(20),@eventdate,120) )
 SELECT @headertext = REPLACE(@headertext , '@hostPCName', @hostPCName)

SELECT @txt = @headertext + @sqlcommand
 SELECT @Path = N'\\servername\sharename\SQLout\' + @objecttype + N'\[dbo].['+ @objectname + N'].sql'

EXEC [dbo].[WriteToTextFile] @Path, @Txt, @appendThis
--could use xp_cmdshell here, to check .sql file into source control, or copy it etc...
FETCH NEXT FROM @c INTO @db, @eventtype, @objectname, @objecttype, @sqlcommand, @eventdate, @HostPCName

END

CLOSE @c
DEALLOCATE @c

 

The DDLChangeLog table is like this:

CREATE TABLE [dbo].[DDLChangeLog](
[LogId] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [VARCHAR](256) NOT NULL,
[EventType] [VARCHAR](50) NOT NULL,
[ObjectName] [VARCHAR](256) NOT NULL,
[ObjectType] [VARCHAR](25) NOT NULL,
[SqlCommand] [VARCHAR](MAX) NOT NULL,
[EventDate] [DATETIME] NOT NULL CONSTRAINT [DF_DDLChangeLog_EventDate] DEFAULT (GETDATE()),
[LoginName] [vARCHAR] (256) NOT NULL,
[HostPCName] [vARCHAR] (250) NULL
)

The DDL Trigger is like this, simple:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [backup_objects]
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION
AS

SET NOCOUNT ON

DECLARE @data xml
SET @data = EVENTDATA()

INSERT INTO DDLChangeLog(databasename, eventtype,
objectname, objecttype, sqlcommand, loginname, HostPCName)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)'),
CAST( HOST_NAME() AS VARCHAR(250))
)

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [backup_objects] ON DATABASE
GO