How can we help you today? How can we help you today?
fth
Activity overview Badges (0) Posts (0) Comments (2)

Activity overview

Latest activity by 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. / comments
  1. Community
  2. SQL Compare Previous Versions
  3. Accessing the body of a stored procedure
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 replac...
0 votes
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 ------------------------------------------------------------------------------- / comments
  1. Community
  2. SQL Compare Previous Versions
  3. 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(nvarc...
0 votes
Total activity
3
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
1