Post reply

Finding Stored Procedures

  • January 11, 2016 at 8:22 pm

    #74802

    Comments posted to this topic are about the item Finding Stored Procedures

  • January 11, 2016 at 10:52 pm

    #1851228

    Nice, simple question, thanks Steve

  • January 11, 2016 at 11:37 pm

    #1851238

    Nice easy one again, a reminder of Information_Schema views too!

    ...

  • January 12, 2016 at 1:44 am

    #1851248

    Nice QOTD.

  • January 12, 2016 at 3:56 am

    #1851260

    Nice question thanks for sharing.

  • January 12, 2016 at 4:35 am

    #1851265

    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

    #1851270

    I rarely use the information_schema objects. I use the sys stuff all the time. Thanks for the question.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • January 12, 2016 at 6:15 am

    #1851283

    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

    #1851284

    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

    #1851320

    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

    #1851354

    Same. "information_schema" - too much typing 🙂

  • January 12, 2016 at 10:59 am

    #1851364

    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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 12, 2016 at 12:54 pm

    #1851400

    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

    #1851417

    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

  • January 12, 2016 at 2:18 pm

    #1851427

    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.

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

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