Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Querying the INFORMATION_SCHEMA Expand / Collapse
Author
Message
Posted Saturday, March 01, 2008 7:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:28 AM
Points: 145, Visits: 193
Comments posted to this topic are about the item Querying the INFORMATION_SCHEMA
Post #462858
Posted Monday, March 03, 2008 7:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 35,955, Visits: 30,245
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #463118
Posted Monday, March 03, 2008 8:24 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 10:10 AM
Points: 74, Visits: 401
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
Post #463168
Posted Monday, March 03, 2008 9:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 1,306, Visits: 778
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





  Post Attachments 
INFO_SCHEMA.GIF (101 views, 109.64 KB)
Post #463244
Posted Monday, March 03, 2008 10:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:32 AM
Points: 2,678, Visits: 2,394
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.........
Post #463260
Posted Monday, March 03, 2008 10:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:28 AM
Points: 145, Visits: 193
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
Post #463265
Posted Monday, March 03, 2008 11:45 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 1:45 PM
Points: 770, Visits: 1,178
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)


SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #463332
Posted Monday, March 03, 2008 12:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:28 AM
Points: 145, Visits: 193
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

Post #463364
Posted Monday, March 03, 2008 4:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 30, 2014 7:54 PM
Points: 21, Visits: 185
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.





Post #463483
Posted Monday, March 03, 2008 4:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:28 AM
Points: 145, Visits: 193
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.
Post #463489
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse