SQL SERVER : Rename all stored procedures in database

I was working on a project and got a requirement from client to rename all stored procedures with  some a specific work as prefix.

I referred some websites and finally found following command,
It renames all stored procedures as single execution.
SELECT 'EXEC sp_rename '+ ''''+s.NAME+ '.' + p.NAME+''','''+s.NAME+ '.' +'your_suffix'+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.

No comments:

Post a Comment