SQL Server Performance

Versioning The SPs

Discussion in 'T-SQL Performance Tuning for Developers' started by thebeginner, Nov 7, 2005.

  1. thebeginner New Member

    Hi folks!
    I needd to keep track of the SPs and Views definitions that are changed within the development domains.
    I used SYSCOMMENTS table to copy the text of SPs and Views into a repository table to keep track of older text if changed.
    I use CHECKSUM function to check the code in syscomments and the one i've created for versioning; if text is changed, the code from syscomments is bakedup into the repository table.


    NOW, i need a way to read the text as it was created by the developer rather than the format held in a single line in SYSCOMMENTS table.

    Also i've to deal with the multilines for a single procedure in SYSCOMMENTS exceeding the 8060 Bytes lenght.
    SP_HELPTEXT also breaks the line for longer rows.



    Howdy!

    -------------------------------------------------------------------
    A DBA seems to work only when the users can't, so i scarcely work!
  2. kpayne New Member

    The text in syscomments is exactly what the developer wrote. It contains all of the formatting. Also, to deal with multiple rows in syscomments, concatenate the text column in the client software.

    All that being said, it is more appropriate to save the functions/procedures as separate script files and use version control on those files. Pulling the text of the objects from syscomments is analagous to reverse-engineering source code from compiled DLLs and EXEs.

    Keith Payne
    Technical Marketing Solutions
    www.tms-us.com
  3. thebeginner New Member

    Hi. thanx for ur reply.
    actually sycomments doesn't seem to have exactly what the developer writes. i need a way to identify the formatting so that i could recognize the new row in order to view the text in readable format.
    an example:

    create procedure usp_test
    as
    select top 1 *
    from sysobjects
    return
    go

    -- no if u select the text it comes in a single line; i need to view it the way i created it:

    select a.text from syscomments a join sysobjects b on a.id=b.id where b.name='usp_test'


    Howdy!

    -------------------------------------------------------------------
    A DBA seems to work only when the users can't, so i scarcely work!
  4. Madhivanan Moderator

    I think sp_helptext 'sp' will show what you want

    Madhivanan

    Failing to plan is Planning to fail
  5. kpayne New Member

    quote:Originally posted by thebeginner

    Hi. thanx for ur reply.
    actually sycomments doesn't seem to have exactly what the developer writes. i need a way to identify the formatting so that i could recognize the new row in order to view the text in readable format.
    an example:

    create procedure usp_test
    as
    select top 1 *
    from sysobjects
    return
    go

    -- no if u select the text it comes in a single line; i need to view it the way i created it:

    select a.text from syscomments a join sysobjects b on a.id=b.id where b.name='usp_test'


    Howdy!

    -------------------------------------------------------------------
    A DBA seems to work only when the users can't, so i scarcely work!

    The newline characters are in there (they will print as two boxes in QA results). If you use the select in a client application and assign the column to a multi-line textbox, it will display correctly.

    You can also test for the newline characters in a stored proc that fetches the text from syscomments. A newline is CR+LF, or CHAR(13) + CHAR(10). You can use any of the TSQL string functions to find the end of line.

    Keith Payne
    Technical Marketing Solutions
    www.tms-us.com

Share This Page