Finding Stored Procedures

Question

Finding Stored Procedures

Viewing 9 posts - 16 through 24 (of 24 total)

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

SQLRNNR SSC Guru Points: 281344 More actions · Answer 1

Hany Helmy (1/12/2016)
Ed Wagner (1/12/2016)
I rarely use the information_schema objects. I use the sys stuff all the time. Thanks for the question.
++1 same...

ditto

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Steve Hendricks Ten Centuries Points: 1026 More actions · Answer 2

I cry foul!!! As James Lean has pointed out, the INFORMATION_SCHEMA.ROUTINES returns both stored procedures AND functions!!! Without the additional filtering, it would be analogous to saying that "SELECT * FROM SYS.OBJECTS" returns all stored procedures. It does return all of the sprocs.... but with the side effect of also returning other schema types. No.... Just no...

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 3

Nice one, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mtassin SSC-Insane Points: 23099 More actions · Answer 4

Steve Hendricks (1/14/2016)
I cry foul!!! As James Lean has pointed out, the INFORMATION_SCHEMA.ROUTINES returns both stored procedures AND functions!!! Without the additional filtering, it would be analogous to saying that "SELECT * FROM SYS.OBJECTS" returns all stored procedures. It does return all of the sprocs.... but with the side effect of also returning other schema types. No.... Just no...

I agree here... I got it right because the other two don't exist and it says to pick 2... but I completely agree with the objection 🙂

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Aleksl-294755 Hall of Fame Points: 3514 More actions · Answer 5

mtassin (1/15/2016)
Steve Hendricks (1/14/2016)
I cry foul!!! As James Lean has pointed out, the INFORMATION_SCHEMA.ROUTINES returns both stored procedures AND functions!!! Without the additional filtering, it would be analogous to saying that "SELECT * FROM SYS.OBJECTS" returns all stored procedures. It does return all of the sprocs.... but with the side effect of also returning other schema types. No.... Just no...
I agree here... I got it right because the other two don't exist and it says to pick 2... but I completely agree with the objection 🙂

Completely agree

Sergiy SSC Guru Points: 110209 More actions · Answer 6

Actually, none of the answers is correct.

As it was mentioned, INFORMATION_SCHEMA.ROUTINES lists not only procedures but functions as well. Without a filtering ROUTINE_TYPE in the query - it's not a correct answer.

Apart from that - everyone has forgotten about "a special kind of stored procedure", as it's defined in msdn, - a trigger.

None of the queries includes those "stored procedures that automatically execute when an event occurs".

https://msdn.microsoft.com/en-nz/library/ms189799.aspx

So, the correct answer on this QOD must be "None".

_____________
Code for TallyGenerator

deroby SSC-Addicted Points: 416 More actions · Answer 7

It gets worse and worse: on my case-sensitive server SELECT * FROM information_schema.ROUTINES doesn't even work!

(I only picked it because I had to pick 2 but it still felt like some trick question =)

PS: and I hardly ever use the INFORMATION_SCHEMA views but prefer their [font="Courier New"]sys.*[/font] counterparts too... it's just what you're used to I guess. I assume they both offer the exact same information but simply in a different way.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 8

Sergiy (1/24/2016)
Actually, none of the answers is correct.
As it was mentioned, INFORMATION_SCHEMA.ROUTINES lists not only procedures but functions as well. Without a filtering ROUTINE_TYPE in the query - it's not a correct answer.
Apart from that - everyone has forgotten about "a special kind of stored procedure", as it's defined in msdn, - a trigger.
None of the queries includes those "stored procedures that automatically execute when an event occurs".
https://msdn.microsoft.com/en-nz/library/ms189799.aspx
So, the correct answer on this QOD must be "None".

While that documentation has that term, I will disagree that a trigger is a stored procedure.

Breaking it down to how each is created:

Create procedure blah

versus

Create trigger blah on table

Is a trigger "stored"? Yes

Is a trigger a "procedure" (as in a process)? sure

But a "stored procedure" is not what I would call it. I would say the article is being a little loose in how the term is being used. E.g. It appears to me that instead of a proper name of "stored procedure" the article is trying to convey an adjective with a verb by saying it is a stored process.

If it truly meant for it to be a type of "stored procedure" I am sure the syntax to create the trigger would be more like the stored procedure syntax...

Create procedure blah as trigger on table

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 9

akljfhnlaflkj

SSC Guru

Points: 76202

February 17, 2016 at 4:44 pm

#1859425

Good question, thanks.