Finding Stored Procedures

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

  • Nice, simple question, thanks Steve

  • This was removed by the editor as SPAM

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

    ...

  • Nice QOTD.

  • Nice question thanks for sharing.

  • 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

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

  • 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

  • Good question, know 1 of them, had to guess the 2nd, thanx.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • 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.

  • Same. "information_schema" - too much typing 🙂

  • 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
  • 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;-)

  • 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