Professional Web Applications Themes

Another Q abt Information_schema.routines - Microsoft SQL / MS SQL Server

Ricky: The text of a function is stored in the syscomments table, [text] column. This has a datatype of nvarchar(4000), and when the body is larger than that, a second row (or more) is created. The syscomments.colid column indicates the sequence of the text "components". You can see how INFORMATION_SCHEMA.ROUTINES retrieves its information by using EM and looking at its definition in the master db. HTH Vern   udf.  body ...

  1. #1

    Default Another Q abt Information_schema.routines

    Ricky:

    The text of a function is stored in the syscomments table,
    [text] column. This has a datatype of nvarchar(4000), and
    when the body is larger than that, a second row (or more)
    is created. The syscomments.colid column indicates the
    sequence of the text "components".

    You can see how INFORMATION_SCHEMA.ROUTINES retrieves its
    information by using EM and looking at its definition in
    the master db.

    HTH
    Vern
     
    udf. 
    body 
    Vern Guest

  2. #2

    Default Re: Another Q abt Information_schema.routines

    > But that field did not returned the whole body of the udf. 

    Return to where, Query yzer, EM, some 3rd party app? Did you try
    DATALENGTH() instead of LEN()? Are you sure that you have maximized the
    output pane of Query yzer (see http://www.aspfaq.com/2272)?

    The information_schema.routines view holds a maximum datalength of 8000
    bytes for the definition of a proc or udf. So, if you really, really,
    really need to have a UDF that exceeds this limitation, *and* you really
    need to later retrieve that programmatically, you'll have to deal with the
    multiple rows coming back from a query against syscomments:

    SELECT text
    FROM syscomments
    WHERE id = OBJECT_ID('functionName')
    ORDER BY colid

    Please do not rely on this methodology in production code, as the system
    tables' structure, functionality, and even existence is likely to change in
    a future version of SQL Server, and this change will break your code when
    you migrate...


    Aaron Guest

  3. #3

    Default Re: Another Q abt Information_schema.routines

    > Does sp_helptext '<udfname>' return the fulltext ?

    Yes.


    Aaron Guest

  4. #4

    Default Re: Another Q abt Information_schema.routines

    Yes Dinesh, sp_helptext did return all the fulltext.

    Thanks
     
    sp_helptext '<udfname>' return the [/ref]
    query [/ref]
    udf. [/ref]
    body 
    >
    >
    >.
    >[/ref]
    Ricky Guest

  5. #5

    Default Re: Another Q abt Information_schema.routines

    Aaron,

    Yes, I did maximized the output pane of Query yzer.
    Interesting thing is when I used len it returned 4000 but
    when I used datalength it returned 8000.

    Here is the udf:

    CREATE FUNCTION [dbo].[Met_udf_PITotalNESecWithdrawlFlow]
    ( cPlant char(3), dtStartdatetime datetime,
    dtEndDatetime datetime, Res varchar(20) )
    RETURNS tblNESecWithdrawl table ([datetime]
    smalldatetime, value real) As
    BEGIN
    If res = 'Hourly'
    Insert tblNESecWithdrawl Select fst13ras601.
    [datetime], isnull(fst13ras601.value,0)+isnull
    (fst13ras602.value,0)+isnull(fst14ras603.value,0)+ isnull
    (fst14ras604.value,0)+isnull(fst15ras605.value,0)+ isnull
    (fst15ras606.value,0)+isnull(fst16ras607.value,0)+ isnull
    (fst16ras608.value,0)+isnull(fst17ras609.value,0)+ isnull
    (fst17ras610.value,0)+isnull(fst18ras611.value,0)+ isnull
    (fst18ras612.value,0) as Value from
    fn_PIGetHourlydataForPlant(cPlant, dtStartdatetime,
    dtEnddatetime,'MetNESecFST13Ras601') as fst13ras601,
    fn_PIGetHourlydataForPlant(cPlant, dtStartdatetime,
    dtEnddatetime,'MetNESecFST13Ras602') as fst13ras602,
    fn_PIGetHourlydataForPlant(cPlant, dtStartdatetime,
    dtEnddatetime,'MetNESecFST14Ras603') as fst14ras603,
    fn_PIGetHourlydataForPlant(cPlant, dtStartdatetime,
    dtEnddatetime,'MetNESecFST14Ras604') as
    fst14ras604,fn_PIGetHourlydataForPlant(cPlant,
    dtStartdatetime, dtEnddatetime,'MetNESecFST15Ras605') as
    fst15ras605, fn_PIGetHourlydataForPlant(cPlant,
    dtStartdatetime, dtEnddatetime,'MetNESecFST15Ras606') as
    fst15ras606, fn_PIGetHourlydataForPlant(cPlant,
    dtStartdatetime, dtEnddatetime,'MetNESecFST16Ras607') as
    fst16ras607, fn_PIGetHourlydataForPlant(cPlant,
    dtStartdatetime, dtEnddatetime,'MetNESecFST16Ras608') as
    fst16ras608, fn_PIGetHourlydataForPlant(cPlant,
    dtStartdatetime, dtEnddatetime,'MetNESecFST17Ras609') as
    fst17ras609, fn_PIGetHourlydataForPlant(cPlant,
    dtStartdatetime, dtEnddatetime,'MetNESecFST17Ras610') as
    fst17ras610, fn_PIGetHourlydataForPlant(cPlant,
    dtStartdatetime, dtEnddatetime,'MetNESecFST18Ras611') as
    fst18ras611, fn_PIGetHourlydataForPlant(cPlant,
    dtStartdatetime, dtEnddatetime,'MetNESecFST18Ras612') as
    fst18ras612 where fst13ras601.[datetime] = fst13ras602.
    [datetime] and fst13ras601.[datetime] = fst14ras603.
    [datetime] and fst13ras601.[datetime] = fst14ras604.
    [datetime] and fst13ras601.[datetime] = fst15ras605.
    [datetime] and fst13ras601.[datetime] = fst15ras606.
    [datetime] and fst13ras601.[datetime] = fst16ras607.
    [datetime] and fst13ras601.[datetime] = fst16ras608.
    [datetime] and fst13ras601.[datetime] = fst17ras609.
    [datetime] and fst13ras601.[datetime] = fst17ras610.
    [datetime] and fst13ras601.[datetime] = fst18ras611.
    [datetime] and fst13ras601.[datetime] = fst18ras612.
    [datetime] order by fst13ras601.[datetime]
    Else If res = 'Daily'
    Insert tblNESecWithdrawl Select fst13ras601.
    [datetime], isnull(fst13ras601.value,0)+isnull
    (fst13ras602.value,0)+isnull(fst14ras603.value,0)+ isnull
    (fst14ras604.value,0)+isnull(fst15ras605.value,0)+ isnull
    (fst15ras606.value,0)+isnull(fst16ras607.value,0)+ isnull
    (fst16ras608.value,0)+isnull(fst17ras609.value,0)+ isnull
    (fst17ras610.value,0)+isnull(fst18ras611.value,0)+ isnull
    (fst18ras612.value,0) as Value from
    fn_PIGetDailydataForPlant(cPlant, dtStartdatetime,
    dtEnddatetime,'MetNESecFST13Ras601') as fst13ras601,
    fn_PIGetDailydataForPlant(cPlant, dtStartdatetime,
    dtEnddatetime,'MetNESecFST13Ras602') as fst13ras602,
    fn_PIGetDailydataForPlant(cPlant, dtStartdatetime,
    dtEnddatetime,'MetNESecFST14Ras603') as fst14ras603,
    fn_PIGetDailydataForPlant(cPlant, dtStartdatetime,
    dtEnddatetime,'MetNESecFST14Ras604') as
    fst14ras604,fn_PIGetDailydataForPlant(cPlant,
    dtStartdatetime, dtEnddatetime,'MetNESecFST15Ras605') as
    fst15ras605, fn_PIGetDailydataForPlant(cPlant,
    dtStartdatetime, dtEnddatetime,'MetNESecFST15Ras606') as
    fst15ras606, fn_PIGetDailydataForPlant(cPlant,
    dtStartdatetime, dtEnddatetime,'MetNESecFST16Ras607') as
    fst16ras607, fn_PIGetDailydataForPlant(cPlant,
    dtStartdatetime, dtEnddatetime,'MetNESecFST16Ras608') as
    fst16ras608, fn_PIGetDailydataForPlant(cPlant,
    dtStartdatetime, dtEnddatetime,'MetNESecFST17Ras609') as
    fst17ras609, fn_PIGetDailydataForPlant(cPlant,
    dtStartdatetime, dtEnddatetime,'MetNESecFST17Ras610') as
    fst17ras610, fn_PIGetDailydataForPlant(cPlant,
    dtStartdatetime, dtEnddatetime,'MetNESecFST18Ras611') as
    fst18ras611, fn_PIGetDailydataForPlant(cPlant,
    dtStartdatetime, dtEnddatetime,'MetNESecFST18Ras612') as
    fst18ras612 where fst13ras601.[datetime] = fst13ras602.
    [datetime] and fst13ras601.[datetime] = fst14ras603.
    [datetime] and fst13ras601.[datetime] = fst14ras604.
    [datetime] and fst13ras601.[datetime] = fst15ras605.
    [datetime] and fst13ras601.[datetime] = fst15ras606.
    [datetime] and fst13ras601.[datetime] = fst16ras607.
    [datetime] and fst13ras601.[datetime] = fst16ras608.
    [datetime] and fst13ras601.[datetime] = fst17ras609.
    [datetime] and fst13ras601.[datetime] = fst17ras610.
    [datetime] and fst13ras601.[datetime] = fst18ras611.
    [datetime] and fst13ras601.[datetime] = fst18ras612.
    [datetime] order by fst13ras601.[datetime]
    Return
    END

    After querying information_schema.routines, I see upto "If
    res = 'Hourly' " but sp_helptext returns full body.

    Thanks for help

    Ricky

     [/ref]
    udf. 
    >
    >Return to where, Query yzer, EM, some 3rd party app?[/ref]
    Did you try 
    have maximized the 
    http://www.aspfaq.com/2272)? 
    datalength of 8000 
    really, really, 
    *and* you really 
    to deal with the 
    syscomments: 
    code, as the system 
    likely to change in 
    break your code when 
    Ricky Guest

  6. #6

    Default Re: Another Q abt Information_schema.routines

    > Thanks for help

    I'm not sure what more help you want... INFORMATION_SCHEMA.ROUTINES does not
    hold the whole text of the procedure, so you're not going to get it from
    there. You're going to have to use sp_helptext or the syscomments method I
    described.

    A


    Aaron Guest

  7. #7

    Default Re: Another Q abt Information_schema.routines

    Thanks Aaron, Ricky.

    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Ricky" <state.mn.us> wrote in message
    news:025901c35d06$8939f650$gbl... 
    > sp_helptext '<udfname>' return the [/ref]
    > query [/ref]
    > udf. [/ref]
    > body 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Dinesh.T.K Guest

Similar Threads

  1. Problems with information_schema
    By Marcel Gsteiger in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 12th, 06:33 AM
  2. Information_schema.routines Q
    By Ricky in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 7th, 03:46 PM
  3. information_schema
    By Chuck in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 16th, 03:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139