Use OBJECT DEFINITION To Track SQL Server Stored Procedure Changes
From Wiki
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 to see how it works
- 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
Contributed by: --SQLDenis 14:03, 14 June 2008 (GMT)
Part of SQL Server Admin Hacks
