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;