August 2, 2006 at 1:23 pm
In our It shop, we have established standards for SP, in hopes of better quality SP. I was assigned to be the police, does anyone know of a way to determine when a SP is modified. Example would be a query to run, sp, etc, any help would be greatly appreciated.
August 2, 2006 at 1:37 pm
This will give you the create date for all SPs. If someone does an ALTER PROCEDURE, this does not affect the create date, but if you require a DROP/CREATE PROCEDURE to make SP changes, this will get you what you need.
select name, crdate
from sysobjects
where xtype = 'P'
order by crdate desc
August 2, 2006 at 2:10 pm
Thanks so much for you help, although we do modify our SP with Alter.
August 2, 2006 at 10:05 pm
If you must pray at the ALTER, then script each to text files, and Diff the previous and Current versions for each sp_ to decide if it was ALTERed, that or get a crystal ball and ...
Andy
August 3, 2006 at 12:29 am
If you're using SQL 2005, you can put a DDL trigger on the database to catch ALTER PROCEDURE statements.
You can do a checksum over the syscomments table for each stored procedure. If you store the checksum value somewhere youll be able to tell that the procedure changed, though not when, by who nor what the old code was.
Something like this works reasonable well
SELECT
id, OBJECT_NAME(id) AS ObjectName, CHECKSUM_AGG(CHECKSUM(text)) SPCheckSum
FROM syscomments
WHERE (OBJECTPROPERTY(id,'IsProcedure')=1 OR OBJECTPROPERTY(id,'IsView')=1)
AND OBJECT_NAME(id) NOT LIKE 'dt/_%' ESCAPE '/'
GROUP BY syscomments.id
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 3, 2006 at 7:46 am
There are also some tools out there that will monitor the database server. Lugiment is one that comes to mind. It can send emails when an event occurs.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
August 7, 2006 at 9:11 am
You can also create a trigger on syscomments table for update/insert/delete to send a email , that way you would be notified if anyone tries to change something in the procedures. Ofcourse you can program it as per your need to keep an eye on specific procedures and not all.
August 7, 2006 at 9:20 am
That's possible, but very much not recommended.
You can't create triggers on system tables (like syscomments) so to do that, you have to convince SQL that the syscomments table is not a system table while you put a trigger on it.
It's risky and unsupported.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 7, 2006 at 9:24 am
Not only is it unsupported, but I think it may even void your support for that licensed copy.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply