January 11, 2016 at 8:22 pm
Comments posted to this topic are about the item Finding Stored Procedures
January 11, 2016 at 10:52 pm
Nice, simple question, thanks Steve
January 11, 2016 at 10:55 pm
This was removed by the editor as SPAM
January 11, 2016 at 11:37 pm
Nice easy one again, a reminder of Information_Schema views too!
...
January 12, 2016 at 1:44 am
Nice QOTD.
January 12, 2016 at 3:56 am
Nice question thanks for sharing.
January 12, 2016 at 4:35 am
Remember that the INFORMATION_SCHEMA.ROUTINES view also includes any user-defined functions in the database. To just get the list of procs you would need to add a filter:
WHERE ROUTINE_TYPE = 'PROCEDURE'
-----
JL
January 12, 2016 at 5:22 am
I rarely use the information_schema objects. I use the sys stuff all the time. Thanks for the question.
January 12, 2016 at 6:15 am
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...
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
January 12, 2016 at 6:16 am
Good question, know 1 of them, had to guess the 2nd, thanx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
January 12, 2016 at 8:26 am
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...
+++1 yep. I always forget to use information_schema. Good reminder.
January 12, 2016 at 10:24 am
Same. "information_schema" - too much typing 🙂
January 12, 2016 at 10:59 am
Aleksl-294755 (1/12/2016)
Same. "information_schema" - too much typing 🙂
Not really, especially when you have some sort of intellisense activated.
Comparing identical functionality:
--121 characters
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
--128 characters
SELECT SCHEMA_NAME( p.schema_id), p.name, m.definition
FROM sys.procedures p
JOIN sys.sql_modules m ON p.object_id = m.object_id
EDIT: Added schema to the column list.
January 12, 2016 at 12:54 pm
The two queries are not equal,SELECT * FROM information_schema.ROUTINES will return all routines regardless of the type, filtering on ROUTINE_TYPE = PROCEDURE will still return CLR procedures which SELECT * FROM sys.objects AS o WHERE type = 'P' will not do.
😎
BTW I think that both sys.all_sql_modules and sys.sql_modules deserve a place on the list;-)
January 12, 2016 at 1:45 pm
Ken Wymore (1/12/2016)
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...
+++1 yep. I always forget to use information_schema. Good reminder.
Me too.
Tom
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply