Accessing the body of a stored procedure

• 
  Hello Justin,
  
  the INFORMATION_SCHEMA.ROUTINES view only shows the first 4000 characters of any SQL object (you can access the view DDL in the master database):
  

  ROUTINE_DEFINITION = convert(nvarchar(4000),
  	SELECT TOP 1 CASE WHEN encrypted = 1 THEN NULL ELSE com.text END
  	FROM syscomments com 
  	WHERE com.id=o.id 
  	AND com.number<=1 
  	AND com.colid = 1),
  

  
  To view the rest of the code you must use the system tables "sysobjects" and "syscomments" directly. As an example, you can look at the following function I wrote some time ago, it returns the number of occurences of a string in a named object:
  

  Alter Function IRIS_U_CountString (
  	@strObject SysName,
  	@strSearch VarCHar(255)
  ) Returns Int As
  
  /********************************************* IRIS_U_AddObjectDescription'
  
  Procedurnamn:	IRIS_U_CountString 
  Beskrivning:	Utility function to count number of occurences of string in
  		procedure, function or other SQL object
  Beroenden:	sysobjects, syscomments
  
  Created by fth Datum 2005-05-24
  '
  *************************************************************************/ 
  
  
  -------------------------------------------------------------------------------
  Begin	-- MAIN
  -------------------------------------------------------------------------------
  
  	-----------------------------------------------------------------------
  	-- Variables
  	Declare @strT1 NVarCHar(4000)
  	Declare @strT2 NVarCHar(4000)
  	Declare @lngN Int
  	Declare @lngPos Int
  	Declare @lngP1 Int
  
  	Select @strT2='', @lngN=0
  
  	-----------------------------------------------------------------------
  	-- Fetch all text in cursor
  	Declare cur Cursor Local For 
  	Select text
  	From sysobjects o 
  	Inner Join syscomments c On o.id=c.id
  	Where O.name=@strObject
  	Order By colid
  
  	Open cur
  	While 1=1 Begin
  		Fetch Next From cur Into @strT1
  		If @@Error<>0 Or @@Fetch_Status<>0 Break
  
  		-- Find "tail" (if any) of search string from last string
  		Select @strT2=@strT2+SubString(@strT1, 1, Len(@strSearch)-1)
  		If CharIndex(@strSearch, @strT2)<>0 Select @lngN=@lngN+1
  
  		-- Search current string
  		Select @lngPos=0
  		While 1=1 Begin
  			Select @lngPos=CharIndex(@strSearch, @strT1, @lngPos+1)
  			If @lngPos=0 Break
  			Select @lngN=@lngN+1, @lngP1=@lngPos
  		End -- While
  
  		-- Save "tail" of current string for next iteration
  		Select @strT2=Right(@strT1, Len(@strSearch)-1)
  
  	End -- While
  	Close cur Deallocate cur
  
  	Return @lngN
  
  -------------------------------------------------------------------------------
  End -- IRIS_U_CountString
  -------------------------------------------------------------------------------
  

  fth February 07, 2006 07:25

  - 0 +
• 
  Thanks for your reply. The syscomments table was exactly what I needed.
  
  Justin

  JustinPaquin February 08, 2006 11:22

  - 0 +
• 
  No problem, just remember the usual disclaimers regarding accessing system tables directly. Also, in SQL2005, the metadata has gone through some rather radical changes, system tables are all replaced by catalog views for instance.

  fth February 09, 2006 05:05

  - 0 +

Add comment

Please sign in to leave a comment.