What are the challenges you face when working across database platforms? Take the survey

Accessing the body of a stored procedure

JustinPaquinJustinPaquin Posts: 7
edited February 9, 2006 5:05AM in SQL Compare Previous Versions

This question isn't directly related to a Red-Gate producct but as a user of your software I figured that someone here might know the answer to this question. I am trying to apply a regular expression the the bodies of all of the stored procedures in our database. My Perl script uses "INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION" to get the body of the stored procedure but the value returned is an nvarchar(4000).

This of course cuts off some of our longer sp's. Does anyone know of another way to access this data?

Thanks in advance for any help



  • Options
    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
  • Options
    Thanks for your reply. The syscomments table was exactly what I needed.

  • Options
    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.
Sign In or Register to comment.