How can i get all my sql server managment studio activite on my database as a executable tsql log?
We have a large Database (Many Table, SP, Function and…) and our database programers are over 10 person. All of theme can use the database on our main server computer or on his local system.
we want each db programmer can apply his definitions (create table, triger, sp, fn &…) and modifications (on tables, sp, trigers, fn &…) by SQL SERVER MANAGMENT STUDIO (or your suggest tool) and get all of this activities as a TSQL Script at the end of each day.
So, we want a automatic log of our db programers activities as a TSQL script (sequence of sub tsql queries). for example:
My first activity>> I open my Customer table as design mode, by right
click on table and click Design. then i change datatype of ID field
from INT to BigInt. then save
My second activity>> I change
PR_Customer_Insert stored procedure and i execute it.
Now i want an executable log like this:
-- UserName: Ram -- 2013-02-10 10:20:35 Alter Table ALTER TABLE Customer ALTER COLUMN Id TYPE bigint; Go -- UserName: Ram -- 2013-02-10 10:45:00 Drop Sp DROP PROCEDURE dbo.PR_Customer_Insert; -- UserName: Ram -- 2013-02-10 10:45:00 Create sp CREATE PROCEDURE PR_Customer_Insert @id int, @name nvarchar(30) AS INSERT INTO Customer ( @id, @name ) GO
We know compare tow database is a solution but we want access to SSMS Query Pipeline…
Is there any way for this request (an automatically generated log that is an executable TSQL script)?
What is the best solutions for generate this scripts automatically???
What do you think about this idea in a team work on database?
2 Solutions collect form web for “How can i get all my sql server managment studio activite on my database as a executable tsql log?”
Possible use DDL triggers, but on a certain database
--Create table EvtLog CREATE TABLE EvtLog ( LoginName NVARCHAR(100), PostTime DATETIME, EventType NVARCHAR(100), TSQLCommand NVARCHAR(2000) ) GO --Create the DDL trigger CREATE TRIGGER trPreventTblChange ON DATABASE FOR DROP_TABLE, CREATE_TABLE, ALTER_TABLE, DROP_PROCEDURE, CREATE_PROCEDURE, ALTER_PROCEDURE AS DECLARE @Data XML SET @Data = EventData() INSERT EvtLog (LoginName, PostTime, EventType, TSQLCommand) SELECT @Data.value('(/EVENT_INSTANCE/LoginName)', 'nvarchar(100)'), @Data.value('(/EVENT_INSTANCE/PostTime)', 'nvarchar(100)'), @Data.value('(/EVENT_INSTANCE/EventType)', 'nvarchar(100)'), @Data.value('(/EVENT_INSTANCE/TSQLCommand)', 'nvarchar(2000)'); GO
Set up a database DDL trigger and use the EVENTDATA Function to find out more information about what was done and insert that into a table that you can query at the end of the day
CREATE TRIGGER [LogDDL] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @xml XML; SELECT @xml = EVENTDATA(); INSERT ChangeLog ( ObjectName, SQL ) VALUES ( @xml.value('(/EVENT_INSTANCE/ObjectName)', 'sysname'), @xml.value('(/EVENT_INSTANCE/TSQLCommand)', 'nvarchar(max)') );