Post reply

Where does SQL Server store function parameters and body?

  • January 7, 2009 at 2:26 am

    #197949

    [font="Comic Sans MS"]

    Hi All,

    I know that sysobjects stores the names of my t-sql functions. Actually the query

    select * from sysobjects where xtype = 'FN'

    brings to me the functions that I own.

    Does anybody know where SQL Server stores function parameters and body?

    Thanks in advance

    [/font]

  • January 7, 2009 at 2:46 am

    #922373

    Check sys.parameters (for parameters) & sys.sql_modules (for code)

    --Ramesh

  • January 7, 2009 at 2:53 am

    #922378

    [font="Comic Sans MS"]

    Thank you for answering.

    I tried this one:

    select * from sys.sql_modules;

    and I suppose that the definition column contains the body of the function.

    However, I realized that it does not contain the line breaks. The whole value of definition is one line string. How should I recover the body of the function with the line breaks too?

    [/font]

  • January 7, 2009 at 3:00 am

    #922382

    matsinopoulos (1/7/2009)

    [

    However, I realized that it does not contain the line breaks. The whole value of definition is one line string. How should I recover the body of the function with the line breaks too?

    It does contain the line breaks. If you're running the query to grid you'll see one line, that's just how the grid works.

    Change the option in management studio so you get results to text.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 7, 2009 at 3:04 am

    #922384

    The "definition" column has line breaks already but you can't be able to view those in grid mode in SSMS, change it to text mode ([CTRL]+T ) ....

    Edit:

    Gail, you've got quick fingers...:D

    --Ramesh

  • January 7, 2009 at 3:13 am

    #922388

    [font="Comic Sans MS"]

    MANY MANY THANKS to both of you. You have answered my question and now I can go on.

    [/font]

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

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