How to check if sp_rename is done successfully?

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.

Solution

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.