SQL Server Portal

Sequence object is one of the handy objects when we need to maintain one serial number across the tables. I have written few articles on Sequence object earlier in my post.

PROBLEM :
Today, I will share an issue that I came across while dropping sequence object. I created few tables and made a single sequence object as a default serial number in one of the columns in these tables. But once I drop this sequence object it gave me error due to dependency. It seems that I need to go one by one and search each dependency of this sequence object and drop all of them before dropping Sequence Object.

Let me create an example to demonstrate the solution.

-- This script is compatibile with SQL Server 2012 and above.
-- CREATE TABLE
USE tempdb
GO
CREATE TABLE dbo.tbl_sample
( [ID] VARCHAR(8) ,
[Product Name] varchar(50)
)
GO

--CREATE SEQUENCE
USE tempdb
GO
CREATE SEQUENCE dbo.Sample_Seq AS
INT START WITH 1
INCREMENT BY 1 ;
GO

-- CREATE DEFAULT VALUE OF SEQUENCE
USE tempdb
GO
ALTER TABLE dbo.tbl_sample
ADD CONSTRAINT Const_Sample_Seq
DEFAULT (NEXT VALUE FOR dbo.Sample_Seq) FOR [ID];
GO
--TRY TO DROP SEQUENCE OBJECT
DROP SEQUENCE Sample_Seq
GO
--OUTPUT

Msg 3729, Level 16, State 1, Line 1
Cannot DROP SEQUENCE ‘Sample_Seq’ because it is being referenced by object ‘Const_Sample_Seq’.

SOLUTION :
As mentioned above, I created a default constraint to a table using sequence object. The problem started when I tried to drop the sequence. Due to dependency, if one sequence object has been used in multiple tables as a default value, then you need to search each dependency of that sequence object and as usual drop all dependent objects before dropping the sequence object.
You can do it, one finding and dropping one object by one object, simple but hectic solution.
Alternative, you can use give below solution. This solution will prepare script to drop all dependent objects of the any sequence object.

-- This script is compatibile with SQL Server 2012 and above.
USE tempdb
GO
SELECT
'ALTER TABLE '
+ object_name(parent_object_id)
+ ' DROP CONSTRAINT '
+ referencing_entity_name AS [Text]
FROM sys.dm_sql_referencing_entities ('dbo.Sample_Seq', 'OBJECT') re
INNER JOIN sys.default_constraints dc
ON re.referencing_id=dc.object_id
GO
--OUTPUT

Just copy the above result set and execute it as mentioned below and it will drop all your dependent objects related to sequence object. Given below is the script.

-- This script is compatibile with SQL Server 2012 and above.
USE tempdb
GO
ALTER TABLE tbl_sample DROP CONSTRAINT Const_Sample_Seq
GO
--OUTPUT

Once you drop the constraint, you can easily drop the sequence, you will not get the above error.

-- This script is compatibile with SQL Server 2012 and above.
DROP SEQUENCE Sample_Seq
--OUTPUT

Command(s) completed successfully.

Let me know if you came across such issues and its solutions.

Read Full Post »