重置SQL AZURE数据库中存储的表的标识列 [英] RESET IDENTITY COLUMN OF A TABLE STORED IN SQL AZURE DATABASE

问题描述

嗨！

我如何在SQL AZURE中解决这个问题？

HOW CAN I FIX THIS PROBLEM IN SQL AZURE ?

我在SQL Azure中存储了一个表数据库。当我使用azure帐户登录到SQL SERVER MANAGEMENT STUDIO 2017并尝试执行以下查询时，它会抛出一个错误，其中包含：

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:

这是我执行的查询

 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

我认为此时失败 因为它找不到这个对象"sys.identity_columns"当数据库存储在sql azure中时，我不知道这个对象存储在何处。

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'

推荐答案

好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

 

这篇关于重置SQL AZURE数据库中存储的表的标识列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！