T-SQL Tuesday #24: Dude, where's the rest of my procedure?
November 8th, 201125
T-SQL Tuesday #24: Dude, where's the rest of my procedure?
November 8th, 201125
 
 

T-SQL Tuesday #24This month's T-SQL Tuesday is being hosted by Brad Schulz (blog) and the topic is one that should attract a lot of submissions: Procedures and Functions.

Last week, I talked about the case against INFORMATION_SCHEMA – I provided several examples where I feel the INFORMATION_SCHEMA metadata falls short of the SQL Server-specific catalog views, and expressed my belief that you are better off programming consistently against the catalog views all the time, instead of only when the INFORMATION_SCHEMA is lacking. Having known at that time about the T-SQL Tuesday topic, I intentionally left one incriminating piece of evidence out of that discussion, and that is how INFORMATION_SCHEMA.ROUTINES feels about your procedures and functions that are longer than 4,000 characters (8,000 bytes).

In SQL Server 2000, you had three ways to retrieve the definition of a procedure or function: INFORMATION_SCHEMA.ROUTINES, syscomments, and sp_helptext. The problem with sp_helptext is that you can't use it interactively – say you don't know the name of the procedure(s) you're trying to retrieve, but want to base it on a search of the text (e.g. return all the stored procedures that contain 'dbo.foo'). You could get there with either of the views, but in fact all three of these approaches have issues:

  • INFORMATION_SCHEMA.ROUTINES only contains the first 4,000 characters of the procedure body. If your procedure is longer, and the text you're looking for falls outside of that (or even if you just want the whole procedure), you're going to be out of luck. This is because the definition of the view purposely truncates the definition:
     
     ...
    convert(nvarchar(4000),
        object_definition(o.object_id))    AS ROUTINE_DEFINITION,
    ...

    I assume this truncation occurs so that the view still conforms to the standard – so even though your entire procedure body is available, it won't show you the whole thing.

  • syscomments is an odd bird, in that it chops your text up into 4,000-character chunks. So while you can manually piece your procedure definition back together (unlike INFORMATION_SCHEMA.ROUTINES, you can't rely on a search of the text for modules that are longer than 4,000 characters, because your search phrase may only occur in a location where it straddles two rows in the result. I'd dig into the mechanics behind this, but you can't get very far… while you can certainly run the following to see what the view does:
     
    EXEC sys.sp_helptext N'sys.syscomments';

    You can see that it references a system table, sys.sysschobjs, and internal functions like sysconv() and OPENROWSET(TABLE SQLSRC):

    CREATE VIEW sys.syscomments AS
      SELECT o.id AS id,
        convert(smallint, case when o.type in ('P', 'RF') then 1 else 0 end) AS number,
        s.colid, s.status,
        convert(varbinary(8000), s.text) AS ctext,
        convert(smallint, 2 + 4 * (s.status & 1)) AS texttype,
        convert(smallint, 0) AS language,
        sysconv(bit, s.status & 1) AS encrypted,
        sysconv(bit, 0) AS compressed,
        s.text
      FROM sys.sysschobjs CROSS APPLY OpenRowset(TABLE SQLSRC, o.id, 0) s
      UNION ALL
      ...

    While sys.sysschobjs is generally off-limits…

    SELECT * FROM sys.sysschobjs;
    Msg 208, Level 16, State 1
    Invalid object name 'sys.sysschobjs'.

    …if you really want to look at what's in there, you can do so using a DAC connection – and no, this has nothing to do with DACPACs, I am talking about the Dedicated Administrator Connection. Once connected via the DAC, you can take a peek inside of many of these system tables that are usually hidden:


    (click to embiggen)

    But even under DAC, you can't call the other functions – you could easily change sysconv() to CONVERT(), but outside of system scope, you'll still get a syntax error on the OPENROWSET() call. So without knowing some serious SQL Server internals, you can't really determine why or how the definition is internally stored in chunks, or dig any deeper into the source…

  • sp_helptext is another peculiar one. It breaks up your modules by carriage return / line feed pairs, and returns each "line" as a separate row. If you have continuous strings that are longer than 255 characters, it breaks them into 255-character chunks. We can see why by looking at the definition:
     
    EXEC sys.sp_helptext N'sys.sp_helptext';

    I'm not going to reproduce the body here, but you will clearly see that there are a lot of loops, cursors and messy string manipulation routines going on in there (the procedure is 208 lines in SQL Server 2012!), making sure that no output line exceeds 255 characters. Why do they do this? Who knows – very skinny CRT screens back when the procedure was first written? The main point is that, even if you dump the results of this procedure into a #temp table, you have even more of a chance to see your search criteria straddle two rows, leading again to missing results.

More Details

Let's demonstrate these issues with a quick example. Here is a stored procedure that is > 4,000 characters, and happens to have the string 'supercalifragilisticexpialidocious' straddle the 4,000-character mark. I'm using dynamic SQL here so I don't have to actually publish huge long strings on this blog (or expect you to copy them):

 DECLARE @sql nvarchar(max) = N'CREATE PROCEDURE dbo.fooblat
AS
BEGIN
  SET NOCOUNT ON;
 
  SELECT ''' + REPLICATE('x', 3900) + '''';
 
  SET @sql += '  SELECT ''-supercalifragilisticexpialidocious-xxxxxxxx'';
END';

Now, let's see how this procedure looks using the various methods described above.

INFORMATION_SCHEMA.ROUTINES

Here is how the procedure is stored in the INFORMATION_SCHEMA view. I'm going to take the right-most 10 characters to show where the definition of the body gets cut off:

SELECT ROUTINE_DEFINITION, 
       [end] = RIGHT(ROUTINE_DEFINITION, 10), 
       l     = LEN(ROUTINE_DEFINITION),
       dl    = DATALENGTH(ROUTINE_DEFINITION)
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_NAME = N'fooblat';


(click to embiggen)

So, seeing as the whole word isn't even there, and that the metadata has only revealed the first 4,000 characters of our procedure, it should come as no surprise that the following search will yield 0 results:

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%supercalifragilistic%';

Results:


(click to embiggen)

So it looks like I've spoiled the party for INFORMATION_SCHEMA again. Sorry about that.

sys.syscomments

As described above, we can see with the following query that sys.syscomments returns our procedure split up into multiple rows, and our search term once again straddles rows:

SELECT colid, 
  [text],
  l  = LEN([text]),
  dl = DATALENGTH([text])
FROM sys.syscomments 
WHERE id = OBJECT_ID(N'dbo.fooblat');

Results:


(click to embiggen)

Which in turn makes a search for this term useless:

SELECT OBJECT_NAME([id])
    FROM sys.syscomments
    WHERE [text] LIKE N'%supercalifragilistic%';


(click to embiggen)

So, it doesn't seem like sys.syscomments is going to be a useful way to find and retrieve procedure definitions, either.
  

sp_helptext

I won't get into the whole rigamarole of dumping the output of sp_helptext for all stored procedures into a #temp table and searching there, since I think you are all smarter than to try this in the first place. But I will show the output of sp_helptext against our procedure, so you can once again see how the body is chunked out into multiple rows:

EXEC sys.sp_helptext N'dbo.fooblat';


(click to embiggen)

Now it turns out that, in this case, the magic word didn't straddle rows (so a convoluted search process might have still yielded this procedure), but that is just dumb luck. With a forced CR/LF pair every 255 characters, and depending on your coding style, you are certainly bound to see some straddling here that will make searches very unreliable. Again, I don't expect any of you are doing anything like this, but wanted to mention it for completeness.

Alternatives

I wouldn't be writing this post if the intention was just to crap on old-school methods for finding and retrieving procedures and functions. I see lots of folks on Stack Overflow still responding to queries with suggestions to use syscomments or INFORMATION_SCHEMA, even though they may not realize that better alternatives exist. I hope I've convinced you above that these old approaches aren't the way to go. So what should you use instead?

OBJECT_DEFINITION()

Since SQL Server 2005, this has been my go-to place for retrieving or searching the definition of procedures and functions. Now, keep in mind this is much easier to demonstrate when we switch to Results to Text mode (Ctrl+T):

SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.fooblat'));


(click to embiggen)

So you can see that our big long line actually gets returned the way it was intended. This gives me much more confidence about a search, since there are no forced formatting changes or truncation that can screw things up.

Now, if we want to find all stored procedures that contain the text supercalifragilistic? Let's switch back to Results to Grid mode first (Ctrl + D) and then run this query. Using a dedicated view for only procedures prevents us from needlessly looking at all of the other object types:

SELECT [schema] = OBJECT_SCHEMA_NAME([object_id]), name
    FROM sys.procedures
    WHERE OBJECT_DEFINITION([object_id]) LIKE N'%supercalifragilistic%';


(click to embiggen)

Unlike the three methods above, we finally have success! If we want to expand our search to procedures and functions, we can use sys.objects and a filter on type – this yields the same result:

 SELECT [schema] = OBJECT_SCHEMA_NAME([object_id]), name
    FROM sys.objects
    WHERE OBJECT_DEFINITION([object_id]) LIKE '%supercalifragilistic%'
    AND [type] IN ('P', 'IF', 'FN', 'TF');

sys.sql_modules

This is a view that contains the definition and other properties for many types of objects – views, procedures, functions, triggers, etc. Under the covers it uses OBJECT_DEFINITION() to expose the body through the column definition. So, borrowing from the query above, we could use sys.sql_modules instead of referencing the function directly, in case we want to use more convoluted joins or filters, or wanted other properties that come directly from sys.sql_modules:

SELECT [schema] = s.name, o.name
    FROM sys.objects AS o
    INNER JOIN sys.schemas AS s
      ON o.[schema_id] = s.[schema_id]
    INNER JOIN sys.sql_modules AS m
      ON o.[object_id] = m.[object_id]
    WHERE m.[definition] LIKE N'%supercalifragilistic%'
    AND o.[type] IN ('P', 'IF', 'FN', 'TF');

Again, this will yield the same results as above.

Conclusion

Yes, sp_helptext is easy to type, and it can be tough to shake old habits of using INFORMATION_SCHEMA or syscomments. But I think you will be better off overall if you use sys.sql_module and OBJECT_DEFINITION() for all of your metadata access to procedures and functions. They will always return the body of the module as intended, without chunking or unnecessary carriage returns and line feeds.

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

25 Responses

  1. Peter Vandivier says:

    "I won't get into the whole rigamarole of dumping the output of sp_helptext for all stored procedures into a #temp table and searching there, since I think you are all smarter than to try this in the first place."
    Why would you assume I'm smarter than that? That's *exactly what I was doing* before *right now.*

  2. AaronBertrand says:

    @Snamelisch I have never seen either of these symptoms, do you have an explicit repro you could provide, and on what version?

  3. Snamelisch says:

    Hi, I found that the hard way that object_definition does not always return the complete script for an object. Even when the amount of characters is well within the boundaries mentioned.
    One thing is of course (like Chip mentioned), sometimes the name within the code is not equal to the real name of the object.
    But I also experienced the disappearance of lines of code somewhere in the middle. This does not occur when using the functionality of SSMS to Create the code for the same object.
    Is this a known issue and has anyone a solution for it?

  4. Adam Sheppard says:

    I enjoyed your article. I had to join syscomments to itself to search for text across the 4000 character boundaries as far back as SQL Server 7.0. The join condition specified c2.colid = c1.colid + 1, so that a chunk of text and the chunk that follows it were both available. Two 4000 character results are available to search: the full text from the first chunk and a concatenation of the last 2000 bytes of the first chunk and the first 2000 bytes of the following chunk. This lets you search across the boundary. The left join means that the last chunk will have NULL for the text of the following chunk, but ISNULL fixes that. As long as the string you're searching for is less than 2000 bytes in size, this works fine and circumvents the character limits.
    declare @sought varchar(100) = '%search text%'
    select distinct name
    from
    (
       select o.name,
              c1.colid,
              c1.text as text1,
              case when len(c1.text) >= 2001
                   then substring(c1.text, 2001, 2000) + substring(isnull(c2.text, "), 1, 2000)
                   else "
              end as text2
       from syscomments c1
       left join syscomments c2 on c2.id = c1.id and c2.colid = c1.colid + 1
       inner join sysobjects o on o.id = c1.id
    )
    q
    where text1 like @sought or text2 like @sought
    order by name

  5. AaronBertrand says:

    David, I prefer the joins over the functions: http://blogs.sqlsentry.com/aaronbertrand/bad-habits-metadata-helper-functions/

  6. David Walker says:

    If you don't need to limit the object type, you can simplify your sys.modules example to this:
    SELECT [schema] = OBJECT_SCHEMA_NAME([object_id]), OBJECT_NAME([object_id])
       From sys.sql_modules
       WHERE [definition] LIKE N'%supercalifragilistic%'

  7. jimbobmcgee says:

    If I just want to see it it in an ad-hoc query, I tend to wrap my long text output in an XML construct, like so:
    SELECT CONVERT(XML,
     '<?proc –' + CHAR(13) + CHAR(10) +
     OBJECT_DEFINITION(OBJECT_ID('sp_add_alert_internal')) +
     CHAR(13) + CHAR(10) + '– ?>'
    )
    I find the XML representation a bit more forgiving with length.  I think you can even set it in SSMS to be unlimited…

  8. AaronBertrand says:

    @Justin How are you viewing the output? What are you using to measure 7,734 characters?
    Some ideas here maybe:
    http://www.mssqltips.com/sqlservertip/3185/validate-the-contents-of-large-dynamic-sql-strings-in-sql-server/

  9. Justin C says:

    For some reason, object_definition is only returning ~7700 characters. For example, SELECT OBJECT_DEFINITION(OBJECT_ID('ProcedureName')) cuts off at 7,734 characters when the procedure is actually 16,564 characters long.. I've tried running it as results to text, results to file, and results to grid but it always cuts off at that point. Any idea why that is?

  10. jon mourar says:

    Still have the 4000 character limit, though. I'm working on that.

  11. AaronBertrand says:

    @jon I'm not quite sure how the code in the post is failing you, but I can see at least two ways your new code could go wrong:
    (1) INFORMATION_SCHEMA only holds the first 4,000 characters
    (2) PRINT has an 8K limit

  12. jon mourar says:

    I found a way to do it. My first post was not very clear, what I want is to have a way to automate printing ALL my sp's and views, with formatting for readability. I found this on stack overflow:
    http://stackoverflow.com/questions/6817199/export-stored-procedures-through-sql-script
    This one does stored procedures:
    DECLARE MY_CURSOR Cursor
    FOR
    SELECT r.Routine_Definition
    FROM INFORMATION_SCHEMA.Routines r
    OPEN MY_CURSOR
       DECLARE @sproc VARCHAR(MAX)
       FETCH NEXT FROM MY_CURSOR INTO @sproc
       WHILE (@@FETCH_STATUS <> -1)
       BEGIN
           IF (@@FETCH_STATUS <> -2)
           PRINT @sproc
           FETCH NEXT FROM MY_CURSOR INTO @sproc
       END
    CLOSE MY_CURSOR
    DEALLOCATE MY_CURSOR
    GO
    For views:
    DECLARE MY_CURSOR Cursor
    FOR
    SELECT v.view_Definition
    FROM INFORMATION_SCHEMA.views v
    OPEN MY_CURSOR
       DECLARE @sproc VARCHAR(MAX)
       FETCH NEXT FROM MY_CURSOR INTO @sproc
       WHILE (@@FETCH_STATUS <> -1)
       BEGIN
           IF (@@FETCH_STATUS <> -2)
           PRINT @sproc
           FETCH NEXT FROM MY_CURSOR INTO @sproc
       END
    CLOSE MY_CURSOR
    DEALLOCATE MY_CURSORGO

  13. jon mourar says:

    How would I get the stored procedure definition with the carriage returns and line feeds, so I can read it more easily in NotePad or Word after using copy and paste from sql server? My attempts so far only yield long run on text with no formatting.

  14. AaronBertrand says:

    @Geri this is why you use OBJECT_DEFINITION() or sys.sql_modules.definition. The point of the post was to show why you shouldn't be using the methods that truncate at 4000 characters…

  15. Geri says:

    If the procedure is over 4000 characters it chops off the rest of the procedure. Is there a way in which I could get the definition of the entire procedure?

  16. wqw says:

    Brad Schulz approach does not work if there is a special character in the body of some of the procedures (special like CHAR(1))

  17. Chip says:

    I'm trying to collect and manage linked server references for all of the views on a server and I started using OBJECT_DEFINITION to parse out source objects.  It all works well but I noticed that the current view name does not appear in the OBJECT_DEFINITION after the view has been renamed either by right-click renaming in the object browser or using sp_rename.  Fortunately OBJECT_NAME does return the current view name…just don't rely on the OBJECT_DEFINITION.  

  18. Alex Feng says:

    Hi,
    Great post!!
    One question: It seems that "SELECT OBJECT_DEFINITION(OBJECT_ID('input_sp_name'));" does not return the entire procedure body if it has multiple-rows (>100) in the "Results to Text" mode, but works in the "Results to Grid" mode (format is not friendly for reading).

  19. Brad Schulz says:

    Hi Aaron…
    Though I would NEVER use this, I just wanted to mention that it is possible to put together a query that solves the 4000-character limits with sys.syscomments (hope the formatting comes out correctly):
    select name
    from sys.procedures p
    cross apply
     (select code=(select [*]=[text]
                   from sys.syscomments
                   where id=p.[object_id]
                   order by colid
                   for xml path("),type)
                  .value('(./text())[1]','nvarchar(max)')) F_Code
    where code like N'%supercalifragilistic%'
    It's a trick, but it works… the CROSS APPLY "glues together" the 4000-chunk fragments for the id.
    Thanks for the contribution to T-SQL Tuesday!
    –Brad

  20. AaronBertrand says:

    Corrected the code sample and screen shot. Sorry for the confusion.

  21. Aaron Bertrand says:

    Yes, I will correct it soon, I just returned from Sweden. I hope the point is still made that the correct query still yields 0 rows.

  22. David Walker says:

    Yes, Marc has pointed out a critical typo in the article.  

  23. Marc says:

    One would not expect
    SELECT ROUTINE_NAME
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_NAME LIKE '%supercalifragilistic%';
    to return any rows.  I think you meant
    WHERE ROUTINE_DEFINITION LIKE '%supercalifragilistic%';
    whose failure would be more disappointing.

  24. Greg M Lucas says:

    Aaron,  I used to use OBJECT_DEFINITION() then when I moved to a different client lost a bunch of frequently used troubleshooting scripts – including that one.  And then for the life of me could never remember the function name when I needed it (and sp_helptext was there like an old friend).  Thanks for the reminder, I'll add this back to my list of GOTO scripts 🙂