While I am in full agreement that source control is the right way to do this, I also understand that not all environments are disciplined enough to rely on that alone (if at all), and that sometimes changes have to made directly to keep the app running, save a client, what have you.
You can use a DDL trigger to keep all revisions in a table in a separate database (and of course back up that database frequently). Assuming you have a utility database:
USE Utility;
GO
CREATE TABLE dbo.ProcedureChanges
(
EventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
EventType NVARCHAR(100),
EventDDL NVARCHAR(MAX),
DatabaseName NVARCHAR(255),
SchemaName NVARCHAR(255),
ObjectName NVARCHAR(255),
HostName NVARCHAR(255),
IPAddress VARCHAR(32),
ProgramName NVARCHAR(255),
LoginName NVARCHAR(255)
);
Now in your database, first let's grab what we'll call "initial control" - the current version of the stored procedures:
USE YourDB;
GO
INSERT Utility.dbo.ProcedureChanges
(
EventType,
EventDDL,
DatabaseName,
SchemaName,
ObjectName
)
SELECT
N'Initial control',
OBJECT_DEFINITION([object_id]),
DB_NAME(),
OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
FROM
sys.procedures;
Now to capture subsequent changes, add a DDL trigger to the database:
USE YourDB;
GO
CREATE TRIGGER CaptureStoredProcedureChanges
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32);
SELECT @ip = client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;
INSERT Utility.dbo.ProcedureChanges
(
EventType,
EventDDL,
SchemaName,
ObjectName,
DatabaseName,
HostName,
IPAddress,
ProgramName,
LoginName
)
SELECT
@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME();
END
GO
Over time it will become easy to see and compare changes to procedures, watch new procedures get added to the system, see procedures get dropped, and have a good idea of who to talk to about any of these events.
More information here:
http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/
Based on past interactions, I'm pretty sure that you're at least on SQL Server 2008R2. You could try filtering on the output of an Extended Event session. Here's a basic one to get you started.
CREATE EVENT SESSION query_check ON SERVER
ADD EVENT sqlserver.sql_statement_starting(
ACTION (sqlserver.database_id, sqlserver.sql_text)),
ADD EVENT sqlserver.sql_statement_completed(
ACTION (sqlserver.database_id, sqlserver.sql_text)),
ADD EVENT sqlserver.sp_statement_starting(
ACTION (sqlserver.database_id, sqlserver.sql_text)),
ADD EVENT sqlserver.sp_statement_completed(
ACTION (sqlserver.database_id, sqlserver.sql_text))
ADD TARGET package0.asynchronous_file_target(SET filename=N'D:\temp\monitor.xel',max_file_size=(5),max_rollover_files=(4))
--,ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON);
You can start it like this:
ALTER EVENT SESSION query_check ON SERVER STATE=START;
Run your query a few times and then stop the event session:
ALTER EVENT SESSION query_check ON SERVER STATE=STOP;
Then you can query it like this:
SELECT
fired_event = event_data.value('(/event/@name)[1]','nvarchar(25)'),
fired_event_time = event_data.value('(/event/@timestamp)[1]','datetime2(0)'),
event_database_id = event_data.value('(/event/action[@name=''database_id''])[1]','int'),
event_sql_text = event_data.value('(/event/action[@name=''sql_text''])[1]','nvarchar(max)')
FROM (
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(N'D:\temp\monitor*.xel',
N'D:\temp\monitor*.xem',
NULL, NULL)
) events
You can capture more data, you can choose what you'd like from here:
SELECT p.name AS package_name,
o.name AS action_name,
o.description
FROM sys.dm_xe_packages AS p
JOIN sys.dm_xe_objects AS o
ON p.guid = o.package_guid
WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
AND o.object_type = 'action'
So if you wanted to capture login information, you'd modify your ACTION
s from
ACTION (sqlserver.database_id, sqlserver.sql_text)),
to
ACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.username)),
I'd actually recommend against filtering on the query text because it's expensive. Instead, I'd try to find the correct username. If you don't think this will work, then I'd filter based on the query text of the result XML file. In fact, only in SQL Server 2012 can you filter on the statement text anyway (it's called a predicate).
If you wanted to perhaps capture data from a specific username you'd add WHERE
to each EVENT
that you'd like to filter.
ADD EVENT sqlserver.sql_statement_starting(
ACTION (sqlserver.database_id, sqlserver.sql_text)
WHERE (sqlserver.username = N'{{ your username here }}')
)
Best Answer
Regarding your question:
I'm not aware of a more efficient or recommended method, but I believe I did validate the basic code you are using.
I created 5 identical stored procedures (SP1 - SP5) that each have a
WAITFOR '00:01:00'
to force the procedure to wait for 1 minute before exiting.I tweaked your original Powershell code to only select stored procedures where the name was
like SP%
to make sure that only SP1 - SP5 would be selected.I started your Powershell script and immediately went back into SSMS to execute sp_WhoIsActive so I could see whether the stored procedures were being executed in parallel and noted that all 5 stored procedures were executing.