sql server stored procedure check if exists table in other database and rename it

Question

Have 2 databases: MAIN and IP2LOCATION

in MAIN, I have the following stored procedure:

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

that does not work properly:

if db11_new does not exists, it (correctly) creates it, but if it exists.. I get

There is already an object named 'db11_new' in the database.

therefore it seems there is something wrong in

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

and also at the end of procedure with the 2 Rename I get (always) the following answer

Msg 15248, Level 11, State 1, Procedure sp_rename, Line 359 Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.

it seems problem is because the sproc is not stored into ip2location DB but in another database..

can suggest a solution, considering that I would prefer to keep all sprocs in MAIN DB, since have all other there?

Thanks

Answer 1

therefore it seems there is something wrong in
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[ip2location].[dbo].[db11_new]') AND type in (N'U'))

Your analysis is correct. The sys.objects catalog view will return objects in the current database context (MAIN). Although you could just use a 3-part name (ip2location.sys.objects), I suggest you simply check for a NULL OBJECT_ID function result:

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 and sp_rename are local objects. Try to use this:

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

and

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

Maybe it helps...

Alternatively, when you wanna do things in another database than the current one, you can write you code in dynamic sql and then execute it directly in the other database.

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

Answer 3

I have tested this query (without csv upload)

At first I remove every reference to 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

First run:

I have no db11* tables. Execution brings me:

Msg 15248, Level 11, State 1, Procedure sp_rename, Line 401 [Batch Start Line 2] Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong. Caution: Changing any part of an object name could break scripts and stored procedures.

That means that db11_new was created, and than renamed in db11, but db11_old wasn't found, so I got this error. I get db11 table in my DB.

Second run:

Caution: Changing any part of an object name could break scripts and stored procedures. Caution: Changing any part of an object name could break scripts and stored procedures.

That means all was created and renamed.

Third run:

Msg 15335, Level 11, State 1, Procedure sp_rename, Line 509 [Batch Start Line 2] Error: The new name 'db11_old' is already in use as a OBJECT name and would cause a duplicate that is not permitted. Msg 15335, Level 11, State 1, Procedure sp_rename, Line 509 [Batch Start Line 2] Error: The new name 'db11' is already in use as a OBJECT name and would cause a duplicate that is not permitted.

So every next re-run You will get this same errors.

My suggestion is to do something about db11_old.

Answer 4

Thanks to Reboon and Dan Guzman here the solution, little improved:

CREATE PROCEDURE dbo.spA_Update_IP2Location_DB11_from_CSV
AS
BEGIN

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]) ON [PRIMARY]

    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' )

BEGIN TRANSACTION
    EXEC ip2location.dbo.sp_rename N'dbo.db11', N'db11_old'
    EXEC ip2location.dbo.sp_rename N'dbo.db11_new', N'db11'
    IF  OBJECT_ID(N'[ip2location].[dbo].[db11_old]', 'U') IS NOT NULL
        BEGIN
            DROP  TABLE ip2location.dbo.db11_old
        END
COMMIT TRANSACTION  
END