Search

Tuesday, October 4, 2011

Rename all stored procedures in database


Below is the query to rename all stored procedures with some a specific word as prefix.

SELECT 'EXEC sp_rename '+ ''''+s.NAME+ '.' + p.NAME+''','''+s.NAME+ '.' +'your_prefix'+p.NAME+'''' FROM SYS.PROCEDURES p ,SYS.SCHEMAS s WHERE p.schema_id = s.schema_id

Warning: Executing this command is dangerous because it may rename system stored procedures too. To avoid such disaster, don't forget to include WHERE condition.

2 comments:

  1. Arun,

    Thank you for your code.

    A note, you used "your_suffix" in your code where you meant "your_prefix"

    ReplyDelete
    Replies
    1. Hi Phil,

      Yes I had made a mistake. It will be 'Your_Pefix'

      thanks for information.

      Delete