Professional Web Applications Themes

Information_schema.routines Q - Microsoft SQL / MS SQL Server

I am defining udfs for calculations that can be used multiple times at different places without rewritting the whole calculation again and again. I need to capture the body of the udf defined to search for any words that begin with 'Met%', 'Sen%' etc. I thought Information_schema.routines view would give me the contents of the defined udfs under Routine_definition field. I realized this view is in the master database. Does this mean I have to define my udfs in master database in order to use this view to return the contents of my defined udfs in order to do the ...

  1. #1

    Default Information_schema.routines Q

    I am defining udfs for calculations that can be used
    multiple times at different places without rewritting the
    whole calculation again and again.

    I need to capture the body of the udf defined to search
    for any words that begin with 'Met%', 'Sen%' etc. I
    thought Information_schema.routines view would give me the
    contents of the defined udfs under Routine_definition
    field. I realized this view is in the master database.
    Does this mean I have to define my udfs in master database
    in order to use this view to return the contents of my
    defined udfs in order to do the above said search ??

    If not, I did define the view parameters as following:

    routine_catalog = 'Reporting' (this is my database name)
    routine_type = 'Function'
    routine_name = 'Met_udf_PITotalNESecMixLiquorFlow'

    but I get no records at all when I query the
    Information_schema.routines view with aboe mentioned
    parameters i.e.

    select routine_definition from
    master.information_schema.routines where routine_catalog
    = 'Reporting' and routine_type = 'Function' and
    routine_name = 'Met_udf_PITotalNESecMixLiquorFlow'

    Thanks for any help.

    Ricky
    Ricky Guest

  2. #2

    Default Re: Information_schema.routines Q

    Nope, you gotta query it like this...

    select *
    from northwind.information_schema.routines



    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net



    "Ricky" <state.mn.us> wrote in message
    news:0bc901c35cf0$6dbaa200$gbl... 


    oj Guest

  3. #3

    Default Re: Information_schema.routines Q

    > select routine_definition from 

    Why the master prefix? Try it without...


    Aaron Guest

  4. #4

    Default Information_schema.routines Q

    Thanks Guys...

     
    the 
    database 
    Ricky Guest

Similar Threads

  1. View definition truncated in information_schema
    By Eric E in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 27th, 07:28 PM
  2. Problems with information_schema
    By Marcel Gsteiger in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 12th, 06:33 AM
  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