Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Use OBJECT DEFINITION To Track SQL Server Stored Procedure Changes

From Wiki

Jump to: navigation, search

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

tsqlLine number Off | Hide | Select all
  1. USE master
  2. GO
  3. --Let's Create a New Database
  4. CREATE DATABASE TestProcCode
  5. GO
  6.  
  7. USE TestProcCode
  8. GO
  9.  
  10.  
  11. --proc0
  12. CREATE PROC proc0
  13. AS
  14. SELECT CURRENT_TIMESTAMP
  15. GO
  16.  
  17. --proc1
  18. CREATE PROC proc1
  19. AS
  20. SELECT GETDATE()
  21. GO
  22.  
  23.  
  24. --proc2
  25. CREATE PROC proc2
  26. AS
  27. SELECT HOST_NAME()
  28. GO
  29.  
  30. --create the proc changes table, bad name I know
  31. CREATE TABLE ProcChanges (ID INT IDENTITY,RunDate DATETIME,
  32. ProcName VARCHAR(100),ProcCode VARCHAR(MAX))
  33. GO
  34.  
  35. --insert all the procs that exist now
  36. INSERT INTO ProcChanges
  37. SELECT '20060410',SPECIFIC_NAME,OBJECT_DEFINITION( OBJECT_ID(SPECIFIC_NAME))
  38. FROM INFORMATION_SCHEMA.ROUTINES
  39. GO
  40.  
  41.  
  42. --Let's change proc2
  43. ALTER PROC proc2
  44. AS
  45. SELECT HOST_ID()
  46. GO
  47.  
  48. --proc 3 is new
  49. CREATE PROC proc3
  50. AS
  51. SELECT SUSER_SNAME()
  52. GO
  53.  
  54. --proc 1 is deleted
  55. DROP PROCEDURE proc1
  56. GO
  57.  
  58. --insert all the procs that exist now
  59. INSERT INTO ProcChanges
  60. SELECT '20060411',SPECIFIC_NAME,OBJECT_DEFINITION( OBJECT_ID(SPECIFIC_NAME))
  61. FROM INFORMATION_SCHEMA.ROUTINES
  62. GO
  63.  
  64.  
  65.  
  66. --grab all deleted procs
  67. SELECT 'Deleted',p1.*
  68. FROM ProcChanges p1
  69. LEFT OUTER JOIN ProcChanges p2 ON p1.ProcName =p2.ProcName
  70. AND p2.RunDAte ='20060411'
  71. WHERE p1.RunDAte ='20060410'
  72. AND p2.ID IS NULL
  73.  
  74.  
  75. --grab all added procs
  76. SELECT 'Added',p1.*
  77. FROM ProcChanges p1
  78. LEFT OUTER JOIN ProcChanges p2 ON p1.ProcName =p2.ProcName
  79. AND p2.RunDAte ='20060410'
  80. WHERE p1.RunDAte ='20060411'
  81. AND p2.ID IS NULL
  82.  
  83.  
  84. --grab all changed procs
  85. SELECT 'Changed',p1.*
  86. FROM ProcChanges p1
  87. JOIN ProcChanges p2 ON p1.ProcName =p2.ProcName
  88. WHERE p1.RunDAte ='20060410'
  89. AND p2.RunDAte ='20060411'
  90. AND p1.ProcCode <> p2.ProcCode
  91.  
  92. --grab all procs that didn't change
  93. SELECT 'Not Changed',p1.*
  94. FROM ProcChanges p1
  95. JOIN ProcChanges p2 ON p1.ProcName =p2.ProcName
  96. WHERE p1.RunDAte ='20060410'
  97. AND p2.RunDAte ='20060411'
  98. AND p1.ProcCode = p2.ProcCode
  99.  
  100.  
  101. USE MASTER
  102. GO
  103.  
  104.  
  105. --let's clean up this mess ;-)
  106. DROP DATABASE TestProcCode
  107. 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

407 Rating: 2.4/5 (5 votes cast)

Retrieved from "http://wiki.lessthandot.com/index.php/Use_OBJECT_DEFINITION_To_Track_SQL_Server_Stored_Procedure_Changes"

Powered by MediaWiki GNU Free Documentation License 1.2
Valid XHTML | Valid CSS