Post reply

complete object defination from syscomments-sql2000

  • February 10, 2009 at 10:08 am

    #73455

    Every one is familiar with syscomments table in SQL 2000

    there is a column inside= text -nvarchar(4000)

    I need to read defination of all objects like SP and F in my databases

    text column displays all these definations

    some of SP are more >8000 and thats why i cant read their whole definations

    and

    for some other databases its giving me this error

    Cannot create a row of size 8095 which is greater than the allowable maximum of 8060.

    I need complete definations from them.

    --there is a column ctext-varbinary(8000) inside that

    Can i get it from ctext column of the same table?

    how?

    When I tried this select convert(varchar(8000),ctext) as defination from syscomments

    Still its not giving me complete defination starts from 'CREATE PROC...'

    Is that possible with fn_get_sql? How?

    Thanks

  • February 10, 2009 at 4:21 pm

    #941739

    Open BOL and read the topic about syscomments.

    _____________
    Code for TallyGenerator

  • February 11, 2009 at 1:21 pm

    #942455

    let me put it this way..

    i need to decode ctext column from syscomments table

    its not in readable form

    any suggestions for this??

    i cant find in book online..

  • February 11, 2009 at 1:27 pm

    #942456

    It's decoded in column text.

    _____________
    Code for TallyGenerator

  • February 11, 2009 at 1:35 pm

    #942461

    column text is not giving the complete defination.. it has datatype of nvarchar(4000)

    wht if my SP is longer than this?? when i select it from text.. its not complete defination

    thats why i thought let me use ctext.. but its not readable...

  • February 11, 2009 at 1:41 pm

    #942464

    Once again - read BOL.

    _____________
    Code for TallyGenerator

  • February 11, 2009 at 1:44 pm

    #942468

    ok just do this on sql 2000 query analyzer

    select * from syscomments

    are u able to read column ctext?? i know its an actual defination

    but can u read that??

    i wan to read that..

    i hope this explains the situation

  • February 11, 2009 at 1:52 pm

    #942470

    As Sergiy said, read BOL.

    [font="Courier New"]

    Column name Data type Description

    id int Object ID to which this text applies.

    number smallint Number within procedure grouping, if grouped. 0 for entries that are not procedures.

    colid smallint Row sequence number for object definitions longer than 4,000 characters.

    status smallint For internal use only.

    ctext varbinary(8000) Actual text of the SQL definition statement.

    texttype smallint 0 = User-supplied comment.

    1 = System-supplied comment.

    4 = Encrypted comment.

    language smallint For internal use only.

    encrypted bit Indicates whether the procedure is encrypted.

    0 = Not encrypted.

    1 = Encrypted.

    compressed bit Indicates whether or not the procedure is compressed.

    0 = Not compressed

    1 = Compressed

    text nvarchar(4000) Actual text of the SQL definition statement. [/font]

    Note specificially the field colid. If the definition is > 4000 characters it uses adtional rows in the table.

Viewing 8 posts - 1 through 7 (of 7 total)

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