Querying the INFORMATION_SCHEMA

  • Comments posted to this topic are about the item Querying the INFORMATION_SCHEMA

  • Nicely done, Mike. By the way, in SS 2k QA, press the {f4} key and see what comes up. 🙂

    --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)
    Intro to Tally Tables and Functions

  • Mike,

    Good article, this will save a lot of time.

    Bill Soranno
    MCP, MCTS, MCITP DBA
    Database Administrator
    Winona State University
    Maxwell 143

    "Quality, like Success, is a Journey, not a Destination" - William Soranno '92

  • Good stuff, Mike.

    Attached (I hope) is a diagram of the INFORMATION_SCHEMA views that I put together a while back. I've found it quite helpful as a reference for making queries.

    TroyK

  • Mike,

    never knew you could pass parameters into those shortcut macros!!!

    BTW - have tried it on my version of SQL2005 Management Studio and it works OK.

    I was thinking of using it to output the procedure text from sp_helptext when I had highlighted a proc name, but I'm having issues with scope - if I create the proc in Master it tries to look for the highlighted proc in master.

    I'll get there.........

  • To cs_Tryok:

    The image is very cool and very useful. Thank you very much. I am sure all who see it will find value in it as I have.

    -Mike

  • Jeff Moden (3/3/2008)


    Nicely done, Mike. By the way, in SS 2k QA, press the {f4} key and see what comes up. 🙂

    I don't know why SSMS 2005 took out the F4 (search) function

    now I have to reply on 3rd party SQL search tool (such as SqlDBSearch)

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • F4 certainly works. In my humble opinion, it takes too long :).

    While I am typing in SQA and SSMS, I use two macors all of the time:

    sp_help and sp_helptText.

    I used type them out all of the time but that took way too long and too many typos resulted. You see, my fingers are extra-ordinarily fat - sorry for the graphic.

    So I pointed keyboard macros at both of these scripts

    Alt+F1 = sp_help

    CTRL+F1 = sp_helptext

    sp_help comes default as macro Alt+F1. Ctrl+F1 wokrs with sprocs, views, udfs - not tables. Since both reside in MASTER, they will work across all of your databases.

    Good luck,

    -Mike

  • Good work, Mike. Handy image, Troy.

    One thing to keep in mind when using INFORMATION_SCHEMA.ROUTINES to search through stored proc text is that it's limited to the first 4000 characters.

    If you have procedures that are larger, query sys.sql_modules or the underlying sysobjects and syscomments tables.

  • Holy Cow! How did you ever figure that one out! It is great information. Perhaps I should add a sub-routine to my script to take into account the length of the routine_definition field. Thank you for the info.

  • It appears that whatever you hightlight is passed to the stored procedure as a single string. It doesn't parse out commas, and positively objects to quotes.

    So I bent to the wind, and simply parsed out the values from the single input parameter. This makes it uglier to use when not using the keyboard shortcut, but when will that happen?

    Cheers!

  • Yes, indeed, it treats the whole comma delim'd parameter as a string - BUT ONLY IN SSMS.

    It works perfectly in SQA. I can't figure out why. One forum poster said it worked for him in SSMS.

  • Mike DiRenzo (3/10/2008)


    One forum poster said it worked for him in SSMS.

    Yes and it still does!...

    even with multiple parameters and ones delimited by single quotes

    create proc kev_test

    (

    @param1 varchar(50),

    @param2 varchar(50)

    )

    as set nocount on

    select 'the first parameter is ' + @param1 + ' the second is ' + @param2

    assign Ctrl-5 to kev_test

    new window..

    'first param','second param'

    highlight it, press Ctrl-5 and results are

    the first parameter is first param the second is second param

  • Definitely there's some option/setup in SSMS -- even on the builtins, if I put single quotes around the string and press Alt-F1, it gives me the error:

    Incorrect syntax near 'sp_help'.

    I wouldn't have a clue where to go from that, though. MSDN Help has, as it often is, been unhelpful, and I haven't found the magic words for Google.

  • Microsoft SQL Server Management Studio9.00.1399.00

    Microsoft Analysis Services Client Tools2005.090.1399.00

    Microsoft Data Access Components (MDAC)2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)

    Microsoft MSXML2.6 3.0 4.0 5.0 6.0

    Microsoft Internet Explorer7.0.5730.11

    Microsoft .NET Framework2.0.50727.832

    Operating System5.1.2600 (XP Pro 2002 SP2)

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

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