Here's the summary for it.
Over the years I have reviewed different source controls and have implemented TFS. I'm a DBA at heart it's not nice nice not being able to have a source control for database development especially that clients cannot afford the expensive ones.
I came upon a database trigger post which I modified to make a rough version control for our team.It involves 2 things
1. Create a Trigger on the database where you want versioned
2. Create a database to save the versions of the things your working on. The DB need only contain 1 specific table.How does it work
1. This tool saves every time you make changes on your objects -- assuming that the way you save your changes is by running it.
These include the following
- Stored procs
2. The database trigger event takes care of the saving. Everytime you press F5 on a alter proc, it will save a copy of your work on the target table which is preferably on another database on the save server.
3. To retrieve the data, you just need to query the data in the table.
The Database Trigger
CREATE TRIGGER [DDLTrigger_Sample]
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,ALTER_SCHEMA, ALTER_FUNCTION,CREATE_FUNCTION,DROP_FUNCTION,ALTER_VIEW,CREATE_VIEW,DROP_VIEW,RENAME,ALTER_TABle,CREATE_TABLE
SET NOCOUNT ON;
@EventData XML = EVENTDATA();
@ip VARCHAR(32) =
WHERE session_id = @@SPID
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
ENABLE TRIGGER [DDLTrigger_Sample] ON DATABASE
CREATE TABLE [dbo].[DDLEvents](
[EventDate] [datetime] NOT NULL,
[EventType] [nvarchar](64) NULL,
[EventDDL] [nvarchar](max) NULL,
[EventXML] [xml] NULL,
[DatabaseName] [nvarchar](255) NULL,
[SchemaName] [nvarchar](255) NULL,
[ObjectName] [nvarchar](255) NULL,
[HostName] [varchar](64) NULL,
[IPAddress] [varchar](32) NULL,
[ProgramName] [nvarchar](255) NULL,
[LoginName] [nvarchar](255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[DDLEvents] ADD DEFAULT (getdate()) FOR [EventDate]
If you find this interesting, I'd like to take a shot at an article for it. If not, nice to have posted here and shared