用于删除旧版本存储过程和函数的 sql 脚本 [英] sql script to drop old versions of stored procedures and functions

问题描述

我正在寻找编写一个 sql server 脚本以在 sql server 2008 数据库中实现以下功能.

I am looking to write a sql server script to achieve the following in a sql server 2008 database.

我有许多具有以下名称的函数和存储过程:

I have a number of functions and stored procedures with the following names:

• prSP_GetItem.1.0.0
• prSP_GetItem.1.1.0
• prSp_GetItem.1.2.0

如您所见，它们附加了版本号.

As you can see a version number is appended to them.

每次发布​​应用程序的新版本时，都会使用新版本号创建新函数/存储过程.

Each time a new version of the application is released, new functions/stored procedures are created with the new version number.

我想做的是编写一个通用脚本来删除所有旧版本的函数或存储过程.因此，假设当前版本是 1.2.0，所有函数和存储过程都以在此之前的版本号结尾，例如.应删除 1.0.0、1.1.1 等.

What I would like to do is write a generic script to remove all old version of a function or stored procedures. So lets say current version is 1.2.0, all functions and stored procedures ending in a version number before this eg. 1.0.0, 1.1.1 etc should be dropped.

关于如何编写脚本来实现这一目标的任何提示或想法?

Any tips or ideas how I might write a script to achieve this?

推荐答案

您可以以此为起点，看看它能带您走多远.对您的数据库运行它并发布您的观察结果.最后一个 select 只列出最后一列中的 SQL 语句.实际上没有执行任何操作.非常仔细地验证输出.

You can use this as starting point and see how far it can take you. Run it against your DB and post your observations. The last select only lists the SQL statement in the last column. Nothing gets executed actually. Verify the output very carefully.

;With RawData As
(
    SELECT
          name
        , SUBSTRING(name, 1, CHARINDEX('.', name) - 1) AS BaseName
        , CAST(REPLACE(SUBSTRING(name, CHARINDEX('.', name), LEN(name) - CHARINDEX('.', name) + 1), '.', '') AS INT) AS IntVersion
    FROM
        sys.objects
    WHERE
        type = 'P'
        AND
        CHARINDEX('.', name) > 0
        AND
        ISNUMERIC(REPLACE(SUBSTRING(name, CHARINDEX('.', name), LEN(name) - CHARINDEX('.', name) + 1), '.', '')) = 1
)
,GroupedData As
(
    SELECT
          BaseName
        , MAX(CAST(REPLACE(SUBSTRING(name, CHARINDEX('.', name), LEN(name) - CHARINDEX('.', name) + 1), '.', '') AS INT)) AS IntVersion
    FROM
        RawData
    GROUP BY
        BaseName
)
SELECT
    *
    , NULLIF(ISNULL(GD.BaseName, ''), ISNULL(GD.BaseName, '-')) + 'DROP PROCEDURE [' + Name + ']' as SQLs
FROM
    RawData RD
LEFT JOIN
    GroupedData GD
ON
    RD.BaseName = GD.BaseName
    AND
    RD.IntVersion = GD.IntVersion
--WHERE
--  GD.BaseName IS NULL
ORDER BY
      RD.BaseName
    , RD.IntVersion

这篇关于用于删除旧版本存储过程和函数的 sql 脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！