This article is half-done without your Comment! *** Please share your thoughts via Comment ***
The database Sequence Object introduced in SQL Server 2012 and we can use and assign single Sequence object to multiple tables.
In this post, I am sharing a script to find all dependencies of the SQL Server Sequence object so that we can drop a Sequence object quickly.
Below is a small demonstration, in which I created a Sequence object and assigned to two different tables.
Create a Sequence object:
|
1 2 3 4 |
CREATE SEQUENCE dbo.Seq_Sample AS INT START WITH 1 INCREMENT BY 1 ; GO |
Create two different tables:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE dbo.tbl_FirstSample ( Rno INT ,Name VARCHAR(255) ) GO CREATE TABLE dbo.tbl_SecSample ( EmpID INT ,EmpName VARCHAR(255) ) GO |
Assign Sequence object to both the tables:
|
1 2 3 4 5 6 7 8 9 |
ALTER TABLE dbo.tbl_FirstSample ADD CONSTRAINT seq_tbl_FirstSample_Rno DEFAULT (NEXT VALUE FOR dbo.Seq_Sample) FOR Rno; GO ALTER TABLE dbo.tbl_SecSample ADD CONSTRAINT seq_tbl_SecSample_EmpID DEFAULT (NEXT VALUE FOR dbo.Seq_Sample) FOR EmpID; GO |
Now, try to drop the Sequence object:
|
1 2 |
DROP SEQUENCE dbo.Seq_Sample GO |
You will get a below error:
Script to find and generate Alter command for all the dependencies:
|
1 2 3 4 5 6 7 8 9 |
SELECT 'ALTER TABLE ' + object_name(parent_object_id) + ' DROP CONSTRAINT ' + referencing_entity_name AS AlterCommands FROM sys.dm_sql_referencing_entities ('dbo.Seq_Sample', 'OBJECT') AS DR INNER JOIN sys.default_constraints AS DC ON DR.referencing_id=DC.object_id GO |
The result:
Before dropping a Sequence object, we should drop all Sequence object constraint so copy the above result and drop both constraints before dropping a Sequence object.
