Querying the INFORMATION_SCHEMA

Question

Post reply

Querying the INFORMATION_SCHEMA

Mike DiRenzo

SSCrazy

Points: 2013
More actions
March 1, 2008 at 7:37 pm

#182761

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

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

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 1

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)

William Soranno SSCommitted Points: 1713 More actions · Answer 2

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

cs_troyk SSCertifiable Points: 5383 More actions · Answer 3

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

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 4

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

Mike DiRenzo SSCrazy Points: 2013 More actions · Answer 5

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

Jerry Hung SSChampion Points: 12968 More actions · Answer 6

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

Mike DiRenzo SSCrazy Points: 2013 More actions · Answer 7

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

adrian_rf SSC Veteran Points: 275 More actions · Answer 8

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.

Mike DiRenzo SSCrazy Points: 2013 More actions · Answer 9

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.

Wyatt Winghead Old Hand Points: 316 More actions · Answer 10

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!

Mike DiRenzo SSCrazy Points: 2013 More actions · Answer 11

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.

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 12

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

Wyatt Winghead Old Hand Points: 316 More actions · Answer 13

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.

Wyatt Winghead Old Hand Points: 316 More actions · Answer 14

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)