Rename all stored procedures in SQL in a single query-CodePudding

I have more than 200 stored procedures in a SQL Server database.

Our stored procedure name start with SP_{CAPITAL_LETTER}.

Now we want to change all the stored procedure's names to proc_{{small_letter}}.

For example: currently we have a stored procedure called SP_EMPLOYEE_LIST and we want to rename it to proc_employee_list.

Do we have any common way to rename all stored procedures using some T-SQL query instead of renaming them one by one?

Thanks, Hitesh

CodePudding user response：

You can dump a script to do all of this and then just run the script

select STRING_AGG(CAST(
    'exec sp_rename @objname = N'   QUOTENAME(s.name   '.'   p.name, '''')   ', @newname = N'   QUOTENAME(LOWER(STUFF(p.name, 1, 3, 'proc_')), '''')   ', @objtype = ''OBJECT'';'
    AS nvarchar(max)), '
')
from sys.procedures p
join sys.schemas s on s.schema_id = p.schema_id
where p.name like 'sp[_]%';

You can even put that into a variable and run it through sp_executesql if you are that (fool-)hardy.