THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Aaron Bertrand

Aaron is a Product Evangelist for SentryOne, makers of Plan Explorer and a performance monitoring and event management platform for the Microsoft Data Platform and VMware. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features, and also blogs at blogs.sentryone.com and SQLPerformance.com; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at major conferences, user group meetings, and SQL Saturday events worldwide.

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

This 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 views - I provided several examples where I feel the INFORMATION_SCHEMA views fall short of the 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 views fail. 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 sp_helptext '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;
    
    Results:
    Msg 208, Level 16, State 1, Line 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 sp_helptext '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);

SET @sql = 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 = 'fooblat';
Results: 


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:


So it looks like I've spoiled the party for the INFORMATION_SCHEMA views once 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('dbo.fooblat');
Results:

 

Which in turn makes a search for this term useless:

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

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 sp_helptext 'dbo.fooblat';
Results:

 

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 StackOverflow and dba.StackExchange.com 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('dbo.fooblat'));
Results:

 

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 '%supercalifragilistic%';
Results: 

 

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]:
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');
This yields the same results as above.


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 '%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_modules 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.
 

Published Tuesday, November 08, 2011 3:19 AM by AaronBertrand

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Greg M Lucas said:

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

November 8, 2011 2:39 AM
 

gbn said:

November 8, 2011 4:09 AM
 

Marc said:

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.

November 10, 2011 7:22 AM
 

David Walker said:

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

November 12, 2011 5:55 PM
 

Aaron Bertrand said:

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.

November 12, 2011 6:10 PM
 

AaronBertrand said:

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

November 13, 2011 11:37 AM
 

Brad Schulz said:

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

November 16, 2011 12:13 AM
 

Alex Feng said:

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

December 30, 2011 3:01 AM
 

Chip said:

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.  

March 30, 2012 6:21 PM
 

wqw said:

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

October 8, 2012 6:03 AM
 

Geri said:

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?

March 1, 2013 9:53 AM
 

AaronBertrand said:

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

March 1, 2013 10:26 AM
 

jon mourar said:

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.

November 19, 2014 9:52 AM
 

jon mourar said:

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

November 19, 2014 11:06 AM
 

AaronBertrand said:

@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

November 19, 2014 11:22 AM
 

jon mourar said:

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

November 19, 2014 11:53 AM
 

Justin C said:

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?

February 2, 2015 10:57 AM
 

AaronBertrand said:

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

February 2, 2015 11:05 AM
 

jimbobmcgee said:

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

April 9, 2015 1:25 PM
 

David Walker said:

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

May 1, 2015 6:36 PM
 

AaronBertrand said:

May 2, 2015 8:38 PM
 

Adam Sheppard said:

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

June 17, 2015 6:00 PM
 

Snamelisch said:

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?

February 3, 2016 8:30 AM
 

AaronBertrand said:

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

February 5, 2016 9:58 AM
 

Peter Vandivier said:

"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.*

August 11, 2016 12:36 PM

Leave a Comment

(required) 
(required) 
Submit

About AaronBertrand

...about me...

This Blog

Syndication

Archives

Privacy Statement