RESET IDENTITY COLUMN OF A TABLE STORED IN SQL AZURE DATABASE

Question

_{Sunday, April 7, 2019 9:30 AM}

Hi!

HOW CAN I FIX THIS PROBLEM IN SQL AZURE ?

I'm having a table stored in SQL Azure Database.When i login to SQL SERVER MANAGEMENT STUDIO 2017 using azure account and try to execute the following query it throws an error Which says:

"Cannot find a table or object with the name "MyTableName". Check the system catalog."

AND THIS IS A QUERY I EXECUTED

 DECLARE @last_value INT = CONVERT(INT, (SELECT last_value FROM sys.identity_columns WHERE OBJECT_NAME(OBJECT_ID) = 'Cars'));
IF @last_value IS NULL
    BEGIN
        -- Table newly created and no rows inserted yet; start the IDs off from 1
        DBCC CHECKIDENT ('Cars', RESEED, 1);
    END
ELSE
    BEGIN
        -- Table has rows; ensure the IDs continue from the last ID used
        DECLARE @lastValUsed INT = (SELECT ISNULL(MAX(CarID),0) FROM Cars);
        DBCC CHECKIDENT ('Cars', RESEED, @lastValUsed);
    END

I guess it fail at this point  because it can't find this object "sys.identity_columns" and i don't know where this object stored when a database is stored in sql azure.

SELECT last_value FROM sys.identity_columns WHERE OBJECT_NAME(OBJECT_ID) = 'Cars'

All replies (4)

_{Sunday, April 7, 2019 10:03 AM}

Good day Clemence,

Try to use something like bellow code (first confirm the table exists)

IF EXISTS 
    (SELECT * FROM sys.tables WHERE OBJECT_NAME(OBJECT_ID) = 'Cars')
BEGIN
    IF (SELECT COUNT(*) FROM Cars) > 0 BEGIN
        -- Table has rows
        DBCC CHECKIDENT ('Cars', RESEED, 1);
    END
    ELSE BEGIN
        PRINT 'TABLE EXISTS BUT HAS NO ROWS'
    END
END
ELSE BEGIN
    PRINT 'TABLE DOES NOT EXISTS'
END

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

_{Monday, April 8, 2019 5:17 AM}

I disagree with your proposed answer!

 Because not always we will be reseed to 1,

 I think this line of code is not right

 DBCC CHECKIDENT ('Cars', RESEED, 1);

C.Mligo

_{Monday, April 8, 2019 5:51 AM}

Good day Clemence,

>>I disagree with your proposed answer!

1. You cannot disagree with my proposed answer since I did not propose any answer.

2. I cannot read your mind and know what "you might do in the future" or that you "not always we will be reseed to 1". I can only answer about the information which you gave us.

3. There is no issue with the command "DBCC CHECKIDENT ('Cars', RESEED, 1);"

You can test the following code for example.

DROP TABLE IF EXISTS Cars
GO
create table Cars(id INT IDENTITY (2,2))
GO
DBCC CHECKIDENT ('Cars', RESEED, 1);
GO

Please clarify what is the issue that you try to solve since your code in total is not well while the specific DBCC command is well formatted but will return error if the table does not exists. According to your description I understand that what I gave is what you want to get

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

_{Monday, April 8, 2019 10:29 AM}

 Thanks  pituach 

I appreciate your help. Let me clarify my question.

I have a SQL DATABASE containing a single table knows as 'Cars'  stored on MICROSOFT AZURE online.The table has FOUR columns below-;

CarID (int) , Primary Key, Identity(1,1)

Make (varchar(50),

Model (varchar(50)),

Color (varchar(50)),

This table  stores some data that user inputs from a VB.NET application which is connected online to azure.From this application user may insert,update and delete records.

As we know if a record is deleted from this table and then another record inserted the The Value in the identity column 'CarID' will keep increasing (will not reseed).

That's the problem  i want to write some code that will help me reseed the values of the identity column so that the sequence of CarID is in good order.

I tried the following code below which throws this  error which says;

"Msg 2501, Level 16, State 45, Line 5 Cannot find a table or object with the name "Cars". Check the system catalog."

THE CODE I TRIED 

 DECLARE @last_value INT = CONVERT(INT, (SELECT last_value FROM sys.identity_columns WHERE OBJECT_NAME(OBJECT_ID) = 'Cars'));
 IF      @last_value IS NULL
         BEGIN
         -- Table newly created and no rows inserted yet; start the IDs off from 1
         DBCC CHECKIDENT ('Cars', RESEED, 1);
         END
 ELSE
         BEGIN
           -- Table has rows; ensure the IDs continue from the last ID used
          DECLARE @lastValUsed INT = (SELECT ISNULL(MAX(CarID),0) FROM Cars);
          DBCC CHECKIDENT ('Cars', RESEED, @lastValUsed);
  END

What should i fix in my code to make this work ?? Is there Any other way round or technique?

C.Mligo