Post reply

Missing Stored Proc Text in syscomments table

  • March 22, 2007 at 7:25 am

    #175754

    I am putting together a routine to identify on a periodic basis changes to the database schema, jsut to alert me of changes to databse objects.

    To this end I have looked at using the information_schema.* views, but I notice that if a stored proc for instance is over 4000 characters, not all of the code is returned.  This lead me to look at the syscomments table in respect of the offending procedures. 

    If I run a simple query   

    SELECT syscomments, colid

    FROM

    sysobjects,syscomments

    WHERE

    sysobjects.id = syscomments.id

    AND

    sysobjects.type = 'P'

    AND

    sysobjects.name = '[SP name]

     

    and then look at the results, some of the text of the procedure is missing.

     

    Any thoughts on why this should be so, and how I might resolve the issue???

     

  • March 22, 2007 at 7:41 am

    #696358

    Shouldn't be missing.  There should be one row per block of 4000 characters.

     

    No workaround to suggest except a select + concatenate (or print).

  • March 22, 2007 at 7:48 am

    #696363

    I have just found the cause of my problem, I was being a silly arse.  The maximum number of characters per row being returned by QA was insufficient to display all of the text in the field. 

     

    I think I'll just quietly forget about this posting and pretend it never happened.

     

     

  • March 22, 2007 at 10:12 am

    #696427

    , you never know when this will help someone else .

  • March 23, 2007 at 7:52 am

    #696607

    You also need to add an ORDER BY, otherwise you can't be sure the code rows will be returned in sequence:

    SELECT ...

    ORDER BY id, colid

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • March 23, 2007 at 10:51 am

    #696666

    Or just upgrade to SQL 2005 where the entire procedure definition is in the information_schema.routines record or in the sys.sql_modules (replacement for syscomments) record.

    There is also a new function called object_definition() that will return the full text of a procedure, function, view, rule, default, trigger, check constraint, etc.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply