Post reply

Where can I find the definistion of this table value function

  • January 22, 2015 at 8:37 am

    #314051

    Consider this .....

    UPDATE t

    SET t.Anesthesia_Start_DT = ct.Anesthesia_Start_DT

    ,t.Patient_in_room_DT = ct.Patient_in_room_DT

    ,t.Procedure_start_DT = ct.Procedure_start_DT

    ,t.Recovery_Room_Start_DT = ct.Recovery_Room_Start_DT

    ,t.Anesthesia_End_DT = ct.Anesthesia_End_DT

    ,t.Patient_out_of_room_DT = ct.Patient_out_of_room_DT

    ,t.Procedure_End_DT = ct.Procedure_End_DT

    ,t.Case_Duration_Anesthesia_min = ct.Case_Duration_Anesthesia_min

    ,t.Case_Duration_In_Room_min = ct.Case_Duration_In_Room_min

    ,t.Case_Duration_Surgery_min = ct.Case_Duration_Surgery_min

    FROM #tempProp t

    JOIN derived.Case_Times ct

    ON t.MPOG_Case_ID = ct.MPOG_Case_ID

    QUESTION: I understand derived.Case_Times is a table valued function.

    But I don't see that function in the database. May be it is in another database. How can I find which database holds this function ?

    Next question is how did the programmer come up with the name "derived.Case_Times" what is that "derived." means

  • January 22, 2015 at 9:08 am

    #1772275

    Hi,

    You have several possibilites.

    One way would be to use the filter available on object explorer in SSMS.

    Another way would be to use Redgate's SQL Search (it's great).

    Another one would be to query the system views to find this object.

    This doesn't seem to be a function, it might be a table or a view.

    "Derived" is the schema to which Case_Times belongs.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 22, 2015 at 9:14 am

    #1772279

    It's not a function, as there are no parentheses, (), after its name.

    This code will tell you what it is, in the "type_desc" column:

    SELECT o.*

    FROM sys.objects o

    WHERE

    o.name = 'Case_Times' AND

    o.schema_id = (SELECT s.schema_id FROM sys.schemas s WHERE name = 'derived')

    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".

  • January 22, 2015 at 9:35 am

    #1772296

    Could be a synonym as well. Check that, but it's not a function as noted above.

  • January 22, 2015 at 9:44 am

    #1772307

    ScottPletcher: Your SQL worked. It happens to be a VIEW.

    My Next question: How do I get the definition of a view

  • January 22, 2015 at 9:52 am

    #1772314

    Easy to get in SSMS. Right click, click the Script as, create, to new window.

  • January 22, 2015 at 9:53 am

    #1772315

    Or:

    EXEC sp_helptext 'derived.Case_Times'

    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".

  • January 22, 2015 at 10:40 am

    #1772326

    mw112009 (1/22/2015)

    ScottPletcher: Your SQL worked. It happens to be a VIEW.

    My Next question: How do I get the definition of a view

    j

    Apologies for being brutally honest but if you don't know the answer to that question, you really need to get some training before you start doing anything to the database.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 22, 2015 at 1:16 pm

    #1772383

    I mean I know how to get that by going through the menu and drilling down , right click and select "SCRIPT...."

    That's not what I really want. A SQl statement that will pull the definition from the db. You know what I mean 🙂

  • January 22, 2015 at 1:20 pm

    #1772384

    mw112009 (1/22/2015)

    I mean I know how to get that by going through the menu and drilling down , right click and select "SCRIPT...."

    That's not what I really want. A SQl statement that will pull the definition from the db. You know what I mean 🙂

    One option would be like this:

    SELECT definition

    FROM sys.sql_modules

    WHERE object_id = OBJECT_ID('MyView')

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 22, 2015 at 1:48 pm

    #1772394

    mw112009 (1/22/2015)

    I mean I know how to get that by going through the menu and drilling down , right click and select "SCRIPT...."

    That's not what I really want. A SQl statement that will pull the definition from the db. You know what I mean 🙂

    Ah. Sorry. Didn't understand. Luis did it in his post above the same way that I would.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 23, 2015 at 11:33 am

    #1772771

    EXEC SP_HELPTEXT 'schema.viewName';

    This formats the output across multiple lines rather than just one. I find it way handier when copying long definitions to the SSMS window.

    ----------------------------------------------------

  • January 23, 2015 at 11:43 am

    #1772775

    MMartin1 (1/23/2015)

    EXEC SP_HELPTEXT 'schema.viewName';

    This formats the output across multiple lines rather than just one. I find it way handier when copying long definitions to the SSMS window.

    True; it's easier to view/work with if you change to text output before running the command, i.e.:

    Press Ctrl-T

    Then run the command.

    Sorry, I should have included that in the original recommendation. Btw, you can press Ctrl-D to get back to Grid mode.

    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".

  • January 23, 2015 at 1:51 pm

    #1772825

    MMartin1 (1/23/2015)

    EXEC SP_HELPTEXT 'schema.viewName';

    This formats the output across multiple lines rather than just one. I find it way handier when copying long definitions to the SSMS window.

    Unless you're in the TEXT results mode or you store it in a variable, which is what I believed the OP was going for. Could be wrong there, though.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 23, 2015 at 2:04 pm

    #1772830

    Good point on the results to text. I have the habit of throwing the results to grid, doing a click on the select all corner of the results tab, copying and pasting it back to command window where I can see all the color coded keywords and what not. But then again I usually have to read through "pages" of code whenever I encounter a suspicious acting SP or View so any colors, ability to test parts of the code, check syntax, etc... all help . 😛

    ----------------------------------------------------

Viewing 15 posts - 1 through 14 (of 14 total)

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