How to check if sp_rename is done successfully?

Question

I am running the following query:

SELECT * INTO dbo.2015_10_2_cs FROM dbo.2015_10_2

IF NOT EXISTS 
(SELECT type FROM sys.indexes WHERE object_id = object_id('dbo.2015_10_2_cs') 
AND NAME ='cci' AND type = 5)
BEGIN
    CREATE CLUSTERED COLUMNSTORE INDEX cci
    ON dbo.2015_10_2_cs
    DROP TABLE dbo.2015_10_2
    EXEC sp_rename "dbo.2015_10_2_cs" , "dbo.2015_10_2"
END

and I want to make sure that the part where I am renaming the table dbo.2015_10_2_cs to dbo.2015_10_2 is done successfully (without losing any data). The step inside the loop should be surrounded with SQL transaction to keep the process safe and reliable (in case if any step will fail). Could anyone help with this? Thanks in advance.

Why not check if the object exists afterwards? Assuming that `dbo.2015_10_2` is a table, you could do a `SELECT` or `PRINT` statement along the lines of: `SELECT CASE WHEN EXISTS(SELECT * FROM sys.tables t WHERE [name] = 'dbo.2015_10_2') THEN 'Successful' ELSE 'Unsuccessful' END AS [Check];`. — Thom A, Mar 15 '18 at 10:57

score 2 · Answer 1 · answered Mar 15 '18 at 11:30

EXEC sp_rename "dbo.2015_10_2_cs" , "dbo.2015_10_2"

This will not do what you expect. The new table will be named [dbo].[dbo.2015_10_2] if you specify the schema name in the new table name. Renamed tables are implicitly in the existing table's schema since one must use ALTER SCHEMA instead of sp_rename to move an object between schemas.

There are a number of other problems with your script. Because the table name starts with a number, it doesn't conform to regular identifier naming rules and must be enclosed in square brackets or double quotes. The literal parameters passed to sp_rename should be single quotes. You can also check to stored procedure return code to ascertain success or failure. The example below performs these tasks in a transaction with structured error handling.

DECLARE @rc int;
BEGIN TRY
    BEGIN TRAN;
    IF NOT EXISTS 
        (SELECT type FROM sys.indexes WHERE object_id = object_id(N'dbo.2015_10_2_cs') 
        AND NAME ='cci' AND type = 5)
    BEGIN
        CREATE CLUSTERED COLUMNSTORE INDEX cci
        ON dbo.[2015_10_2_cs];
        DROP TABLE dbo.[2015_10_2];
        EXEC @rc = sp_rename 'dbo.[2015_10_2_cs]' , '2015_10_2';
        IF @rc <> 0
        BEGIN
            RAISERROR('sp_rename returned return code %d',16,1);
        END;
    END;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;

Thank you for your answer, it was really helpful :) – alexithymia Mar 16 '18 at 16:30 — alexithymia, Mar 16 '18 at 16:30

EzLo · Accepted Answer · 2018-03-15T11:17:51.433

You can use an EXISTS checking for the tablename and schema.

IF NOT EXISTS (SELECT 'table does not exist' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'2015_10_2'AND TABLE_SCHEMA = 'dbo')
BEGIN

    RAISERROR('The table doesn''t exist!!!!', 16, 1)

END

sp_rename won't make you lose table contents, it will just change the table reference name and update all it's contraints and indexes references. It will also raise an error if the table to rename does not exist. Maybe what you want is to wrap your process in a transaction and rollback if something fails.

EDIT:

For basic transaction handling you can use the following. Please read the documentation for using transaction, it might take a while to know how it works correctly.

IF OBJECT_ID('tempdb..#Test') IS NOT NULL
    DROP TABLE #Test

CREATE TABLE #Test (Number INT)

SELECT AmountRecords = COUNT(1) FROM #Test -- AmountRecords = 0

BEGIN TRY

    BEGIN TRANSACTION

    -- Do your statements here

    INSERT INTO #Test (Number)
    VALUES (1)

    DECLARE @errorVariable INT = CONVERT(INT, 'NotAnInteger!!') -- Example of error: can't convert

    COMMIT

END TRY

BEGIN CATCH -- If something goes wrong

    IF @@TRANCOUNT > 0 -- ... and transaction is still open
        ROLLBACK -- Revert statements from the BEGIN TRANSACTION onwards

END CATCH


SELECT AmountRecords = COUNT(1) FROM #Test -- AmountRecords = 0 (the transaction was rolled back and the INSERT reverted)

Basically you use BEGIN TRANSACTION to initiate a restore point to go back to if something fails. Then use a COMMIT once you know everything is OK (from that point onwards, other users will see the changes and modifications will be persisted). If something fails (you need TRY/CATCH block to handle errors) you can issue a ROLLBACK to revert your changes.

"Maybe what you want is to wrap your process in a transaction and rollback if something fails" exactly. How to do this? — alexithymia, Mar 15 '18 at 11:04
@EzequielLópezPetrucci, the `CATCH` block in your code silently ignores errors. It would probably be best to rethrow the error so that one knows an error occurred. — Dan Guzman, Mar 15 '18 at 12:11

How to check if sp_rename is done successfully?

2 Answers2