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.
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’.
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.