Accessing the body of a stored procedure

JustinPaquin

Hello,

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

Justin

fth

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
-------------------------------------------------------------------------------

JustinPaquin

Thanks for your reply. The syscomments table was exactly what I needed.

Justin

fth

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.