January 12, 2016 at 2:18 pm
Nice question, bad answer.
"SELECT * FROM information_schema.ROUTINES" also returnes functions, if I want a list of stored procedures it should only hold stored procedures.
The most important part of SQL is to bring the exact answer to the question, not the answer and then some, precision is the essens of SQL.
January 12, 2016 at 4:00 pm
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
January 14, 2016 at 5:01 pm
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...
January 15, 2016 at 3:56 am
Nice one, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 15, 2016 at 7:14 am
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 🙂
January 15, 2016 at 8:11 am
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
January 24, 2016 at 9:19 pm
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
January 25, 2016 at 4:29 am
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.
January 25, 2016 at 11:45 am
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
February 17, 2016 at 4:44 pm
Good question, thanks.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply