重命名以特定前缀开头的存储过程。

I have a large number of Stored Procedures that need to be renamed. Luckily, they all start with the same prefix e.g. spPortalxxxxxx

我有大量需要重命名的存储过程。幸运的是，它们都以相同的前缀开始，例如spportalxxxxxxxx。

I need to rename them e.g. v11_spPortalxxxxxx

我需要重命名它们，例如v11_spPortalxxxxxx。

Is there anyway to rename all of the Stored Procedures in one go rather than having to do each one manually?

是否有方法将所有存储过程重新命名，而不是手工操作?

2 个解决方案

#1

You could be tempted to SP_Rename but that can be one the worst things to do, since it doesn't update sys.procedures see this

您可能会受到SP_Rename的诱惑，但这可能是最糟糕的事情，因为它不更新sys。程序看

An approach I could think of is creating a query that drops the old ones and recreate them with the new name.

我可以想到的一个方法是创建一个查询，它会删除旧的，并用新名称重新创建它们。

SELECT 'drop procedure ' + OBJECT_NAME(OBJECT_ID) + ';
go
' + 
REPLACE (OBJECT_DEFINITION(OBJECT_ID), 'spPortal', 'v11_spPortal') + '
go
'
FROM sys.procedures

This query will update also any call to inside the definition...

该查询将更新对定义的任何调用…

I think that you are adding this for versionning, if so you would need to add a WHERE OBJECT_NAME(OBJECT_ID) LIKE 'spPortal%'

我认为您将此添加到versionning中，如果需要，您需要添加一个WHERE OBJECT_NAME(OBJECT_ID)，比如“spPortal%”

Don't forget that you will need to apply GRANT as needed.

别忘了你需要在需要的时候申请拨款。

#2

What you can do is to create the rename statements by a Sql query. Then copy and paste the query result to new query window to execute

您可以通过一个Sql查询创建重命名语句。然后复制并粘贴查询结果到新的查询窗口执行。

select 

'sp_rename ''' +[Routine_Name]+''','''+'v11_'+[Routine_Name]+''''

FROM [INFORMATION_SCHEMA].[ROUTINES]
WHERE [ROUTINE_TYPE] = 'PROCEDURE'
and [Routine_Name] like 'spPortal%'

#1