March 21, 2005 at 11:32 am
I'm trying to get a list of all the SPs that have been modified this year using 'ALTER PROCEDURE'.
Is there anyway to do this?
March 21, 2005 at 11:40 am
You'll have to run a trace and scan for the alter procedure text in it.
March 21, 2005 at 3:05 pm
Another possibility would be to restore a backup of your database from the beginning of the year, and compare the stored procs in the old with the new. I'll leave it up to you how to compare the procedures, but the text is stored in syscomments. By the way, if the text in the procedure is different, but the create date is the same, the procedure was altered. If the text is different and the create date is different, the procedure was dropped and re-created. This doesn't tell you how many times a procedure was dropped and re-created or altered. It'll just tell you that it has been done.
Steve
March 22, 2005 at 12:26 pm
I do not know of a 'clean' way to do this in SQL2K. You'll have better luck in SQL2K5 though with DDL triggers to perform this type of audit.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
March 22, 2005 at 7:19 pm
Yeah, this is some basic functionality that should be in SQL2K, but isn't.
you can get the creationdate, though, although this isn't going to help with "alter proc" commands.
-------------------------------------------------------------------------------
select Name, crdate
from sysobjects
where xtype = 'P'
and crdate > getdate()-365
Signature is NULL
March 23, 2005 at 6:59 am
check this out :
if (object_id('test')) > 0
drop proc test
go
create proc test
as
select null
go
select Name, id, crdate, schema_ver from dbo.SysObjects where name = 'test' and xType = 'P'
go
alter proc test
as
select 1
go
select Name, id, crdate, schema_ver from dbo.SysObjects where name = 'test' and xType = 'P'
The column schema_ver is modified each time the alter proc command is executed.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply