Tracking code changes for stored procedures, functions and tables in MSSQL

Question

CVS

There is a base (mssql 2008 r2), it contains tables, stored procedures and functions. As a result of the ill-conceived architecture, it turned out that at the moment, in order to fix something in the database, it is necessary to do it directly on the “combat” server. This applies to procedures, tables and functions. As a result, it is impossible to track who ruled the base code and at what time.

You need to make sure that all changes are tracked by the version control system.
What are the means to solve this problem?

Thank you.

Answer 1

Yes, the trigger is what you need!
if you need a version control system, then RedGate has a wonderful tool called SQL Source control

Answer 2

Generally elementary. Base trigger.
Step one:
CREATE TABLE [dbo].[tbl_ListChange](
[LoginName] [varchar](2000) NULL,
[HostName] [varchar](100) NULL,
[ObjectName] [varchar](100) NULL,
[ObjectType] [ varchar](100) NULL,
[EventType] [varchar](100) NULL,
[EventSQLCommand] [varchar](max) NULL,
[EventTime] [datetime] NOT NULL,
[XMLChange] [xml] NULL,
[Id] [ int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_tbl_ListChange] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty name =N'MS_Description', value =N'Fully xml command containing everything at all ', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@ level1name=N'tbl_ListChange', @level2type=N'COLUMN',@level2name=N'XMLChange'
GO
Second step: — trigger to the base:
CREATE TRIGGER [tgr_DDLListChange]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
— Karetnikov. 25-03-2010. Monitoring of all changes in the database
SET NOCOUNT ON
DECLARE @dataXML XML
SET @dataXML = EVENTDATA() - received data on the current event.
IF (
SELECT @dataXML.value('
) IS NOT NULL
BEGIN
INSERT dbo.tbl_ListChange
(
LoginName - under whose login
,HostName - on whose machine
,ObjectName - what was changed
,ObjectType - type of the changed object
,EventType - type of change
,EventSQLCommand - full SQL command
,EventTime - how much was changed
, XMLChange - the entire xml command
)
VALUES
(
@dataXML.value('(/EVENT_INSTANCE/LoginName)[1]' ,'varchar(2000)') - under whose login
,HOST_NAME() - on whose machine
,@dataXML. value('(/EVENT_INSTANCE/ObjectName)[1]' ,'varchar(100)') — what is changed
,@dataXML.value('(/EVENT_INSTANCE/ObjectType)[1]' ,'varchar(100)') — modified object type
,@dataXML.value('(/EVENT_INSTANCE/EventType)[1]' ,'varchar( 100)') — type of change
,@dataXML.value('(/EVENT_INSTANCE/TSQLCommand)[1]' ,'varchar(max)') — complete SQL command
,GETDATE()
, @dataXML
) — what time
END
GO was changed

Answer 3

You can try to create a database project in VS2010 and above.
Set the master on your database, put the resulting project under versioned storage (Git, Subversion, ...), then the developers open the project in VS2010, edit the code and publish it on the server only from the project.
Benefits
You can start writing unit tests against the database.
PS
I am developing a large CRM for a construction company, hundreds of tables, everything suits.