Use OBJECT_DEFINITION To Track Procedure Changes

Not everyone uses Visual Source Safe, CVS or Subversion to keep track of proc changes/deletions and/or additions
Of course you could use Red-Gate SQL Compare (I do) But let's say you don't have any of these tools and are using SQL Server 2005, what else can you do?
In SQL Server 2000 you can use
select ROUTINE_DEFINITION,SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES to get the body of the stored procedure, the caveat is that this will only return 4000 characters.
Another way is to use the sp_helptext procedure
In SQL server 2005 this is much easier. There is a new function in town: OBJECT_DEFINITION()

OBJECT_DEFINITION() does return the whole body of a stored procedure
Below is some code (very simple) that will give you an idea of how you could use OBJECT_DEFINITION() to keep track of changes
You will have to setup a job that runs once a day and stores the definition of all the procedures in a table
Then you can do a self join on that table to find added, deleted and changed procedures
You can run the code below in 1 shot if you want

USE master
GO
--Let's Create a New Database
CREATE DATABASE TestProcCode
GO

USE TestProcCode
GO


--proc0
CREATE PROC proc0
AS
SELECT CURRENT_TIMESTAMP
GO

--proc1
CREATE PROC proc1
AS
SELECT GETDATE()
GO


--proc2
CREATE PROC proc2
AS
SELECT HOST_NAME()
GO

--create the proc changes table, bad name I know
CREATE TABLE ProcChanges (ID INT IDENTITY,RunDate DATETIME,ProcName VARCHAR(100),ProcCode VARCHAR(MAX))
GO

--insert all the procs that exist now
INSERT INTO ProcChanges
SELECT '20060410',SPECIFIC_NAME,OBJECT_DEFINITION( OBJECT_ID(SPECIFIC_NAME))
FROM INFORMATION_SCHEMA.ROUTINES
GO


--Let's change proc2
ALTER PROC proc2
AS
SELECT HOST_ID()
GO

--proc 3 is new
CREATE PROC proc3
AS
SELECT SUSER_SNAME()
GO

--proc 1 is deleted
DROP PROCEDURE proc1
GO

--insert all the procs that exist now
INSERT INTO ProcChanges
SELECT '20060411',SPECIFIC_NAME,OBJECT_DEFINITION( OBJECT_ID(SPECIFIC_NAME))
FROM INFORMATION_SCHEMA.ROUTINES
GO



--grab all deleted procs
SELECT 'Deleted',p1.*
FROM ProcChanges p1
LEFT OUTER JOIN ProcChanges p2 ON p1.ProcName =p2.ProcName
AND p2.RunDAte ='20060411'
WHERE p1.RunDAte ='20060410'
AND p2.ID IS NULL


--grab all added procs
SELECT 'Added',p1.*
FROM ProcChanges p1
LEFT OUTER JOIN ProcChanges p2 ON p1.ProcName =p2.ProcName
AND p2.RunDAte ='20060410'
WHERE p1.RunDAte ='20060411'
AND p2.ID IS NULL


--grab all changed procs
SELECT 'Changed',p1.*
FROM ProcChanges p1
JOIN ProcChanges p2 ON p1.ProcName =p2.ProcName
WHERE p1.RunDAte ='20060410'
AND p2.RunDAte ='20060411'
AND p1.ProcCode <> p2.ProcCode

--grab all procs that didn't change
SELECT 'Not Changed',p1.*
FROM ProcChanges p1
JOIN ProcChanges p2 ON p1.ProcName =p2.ProcName
WHERE p1.RunDAte ='20060410'
AND p2.RunDAte ='20060411'
AND p1.ProcCode = p2.ProcCode


USE MASTER
GO


--let's clean up this mess ;-)
DROP DATABASE TestProcCode
GO


What I have shown is very simple, you can expand on this and check for date ranges and improve on this a lot if you need to