sql server存储过程检查其他数据库中是否存在表并重命名它。

Question

有两个数据库: MAIN和IP2LOCATION

总的来说，我有以下存储过程：

CREATE PROCEDURE dbo.Update_IP2Location_DB11_from_CSV
AS
BEGIN
    IF  NOT EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[ip2location].[dbo].[db11_new]') AND type in (N'U'))
        BEGIN
            CREATE TABLE [ip2location].[dbo].[db11_new]
            (
                [ip_from]       bigint          NOT NULL,
                [ip_to]         bigint          NOT NULL,
                [country_code]  nvarchar(2)     NOT NULL,
                [country_name]  nvarchar(64)    NOT NULL,
                [region_name]   nvarchar(128)   NOT NULL,
                [city_name]     nvarchar(128)   NOT NULL,
                [latitude]      float           NOT NULL,
                [longitude]     float           NOT NULL,
                [zip_code]      nvarchar(30)    NOT NULL,
                [time_zone]     nvarchar(8)     NOT NULL,
            ) ON [PRIMARY]

            CREATE INDEX [ip_from] ON [ip2location].[dbo].[db11_new]([ip_from])
        END
    ELSE
        BEGIN
            DELETE FROM [ip2location].[dbo].[db11_new]
        END

    BULK INSERT [ip2location].[dbo].[db11_new]
        FROM 'D:\IP2LOCATION-LITE-DB11.CSV'
        WITH
        ( FORMATFILE = 'C:\inetpub\wwwroot\ws\DB11_ip4.FMT')

    EXEC sp_rename N'dbo.db11', N'db11_old', 'OBJECT'
    EXEC sp_rename N'ip2location.dbo.db11_new', N'db11', 'OBJECT'   
END

这不能正常工作：

如果db11_new不存在，它(正确)就会创建它，但是如果它存在..我得到了

数据库中已经有一个名为“db11_new”的对象。

因此，似乎有些地方出了问题

IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[ip2location].[dbo].[db11_new]') AND type in (N'U'))

在过程的末尾，用2重命名，我得到(总是)下面的答案

Msg 15248，级别11，状态1，过程sp_rename，第359行参数@objname是模棱两可，或者声称的@objtype (对象)是错误的。

问题似乎在于sproc不是存储在ip2location DB中，而是存储在另一个数据库中。

考虑到我更愿意将所有的sprocs保存在主DB中，那么可以提出一个解决方案，因为所有其他的都有吗？

谢谢

Answer 1

因此，如果不存在，似乎存在错误(从sys.objects中选择*，其中object_id = OBJECT_ID(N'ip2location.dbo.db11_new')并键入(N'U'))。

你的分析是正确的。sys.objects目录视图将返回当前数据库上下文(MAIN)中的对象。虽然您可以只使用3部分的名称(ip2location.sys.objects)，但我建议您只检查NULL OBJECT_ID函数的结果：

IF  OBJECT_ID(N'[ip2location].[dbo].[db11_new]', 'U') IS NULL
        BEGIN
            CREATE TABLE [ip2location].[dbo].[db11_new]
            (
                [ip_from]       bigint          NOT NULL,
                [ip_to]         bigint          NOT NULL,
                [country_code]  nvarchar(2)     NOT NULL,
                [country_name]  nvarchar(64)    NOT NULL,
                [region_name]   nvarchar(128)   NOT NULL,
                [city_name]     nvarchar(128)   NOT NULL,
                [latitude]      float           NOT NULL,
                [longitude]     float           NOT NULL,
                [zip_code]      nvarchar(30)    NOT NULL,
                [time_zone]     nvarchar(8)     NOT NULL,
            ) ON [PRIMARY];

            CREATE INDEX [ip_from] ON [ip2location].[dbo].[db11_new]([ip_from]);
        END;
    ELSE
        BEGIN
            DELETE FROM [ip2location].[dbo].[db11_new];
        END;

Answer 2

sys.objects和sp_rename是本地对象。试着使用这个：

IF  NOT EXISTS (SELECT * FROM ip2location.sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[db11_new]') AND type in (N'U'))

和

    EXEC ip2location.sp_rename N'dbo.db11_new', N'db11', 'OBJECT'   

也许会有帮助..。

或者，当您想在另一个数据库中执行比当前数据库更多的事情时，您可以用动态sql编写代码，然后在另一个数据库中直接执行它。

https://msdn.microsoft.com/en-us/library/ms188001.aspx

Answer 3

我已经测试了这个查询(没有csv上传)

首先，我删除了对ip2location的所有引用

CREATE PROCEDURE dbo.Update_IP2Location_DB11_from_CSV
AS
BEGIN
IF  NOT EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'dbo.db11_new') AND type in (N'U'))
        BEGIN
            CREATE TABLE [dbo].[db11_new]
            (
                [ip_from]       bigint          NOT NULL,
                [ip_to]         bigint          NOT NULL,
                [country_code]  nvarchar(2)     NOT NULL,
                [country_name]  nvarchar(64)    NOT NULL,
                [region_name]   nvarchar(128)   NOT NULL,
                [city_name]     nvarchar(128)   NOT NULL,
                [latitude]      float           NOT NULL,
                [longitude]     float           NOT NULL,
                [zip_code]      nvarchar(30)    NOT NULL,
                [time_zone]     nvarchar(8)     NOT NULL,
            ) ON [PRIMARY]

            CREATE INDEX [ip_from] ON [dbo].[db11_new]([ip_from])
        END
    ELSE
        BEGIN
            DELETE FROM [dbo].[db11_new]
        END

    BULK INSERT [dbo].[db11_new]
        FROM 'D:\IP2LOCATION-LITE-DB11.CSV'
        WITH
        ( FORMATFILE = 'C:\inetpub\wwwroot\ws\DB11_ip4.FMT')

    EXEC sp_rename N'dbo.db11', N'db11_old', 'OBJECT'
    EXEC sp_rename N'dbo.db11_new', N'db11', 'OBJECT'   
END
GO

第一次运行：

我没有db11*表。死刑带给我的是：

Msg 15248，级别11，状态1，过程sp_rename，第401号批处理开始行2，参数@objname不明确，或者声称的@objtype (对象)是错误的。注意:更改对象名称的任何部分都可能破坏脚本和存储过程。

这意味着创建了db11_new，并在db11中进行了重命名，但是没有找到db11_old，所以我得到了这个错误。我在DB中得到了db11表。

第二轮：

注意:更改对象名称的任何部分都可能破坏脚本和存储过程。注意:更改对象名称的任何部分都可能破坏脚本和存储过程。

这意味着所有的一切都被创建并重命名了。

第三轮：

Msg 15335，级别11，状态1，过程sp_rename，第509号批处理开始行2错误:新的名称'db11_old‘已经被用作对象名称，并将导致不允许的复制。Msg 15335，级别11，状态1，过程sp_rename，第509号批处理开始行2错误:新的名称'db11‘已经被用作对象名称，并将导致不允许的复制。

所以每次再运行你都会得到同样的错误。

我的建议是对db11_old做点什么。