How can I get the definition of any Sequence
objects in SQL Server? For instance if I want to get the definition of View/Function/Procedure I would use below query
SELECT OBJECT_DEFINITION(tab.OBJECT_ID) FROM SYS.OBJECTS tab WHERE tab.[type] = 'V' /*FOR VIEW*/ SELECT OBJECT_DEFINITION(tab.OBJECT_ID) FROM SYS.OBJECTS tab WHERE tab.[type] = 'P' /*FOR PROCEDURE*/ SELECT OBJECT_DEFINITION(tab.OBJECT_ID) FROM SYS.OBJECTS tab WHERE tab.[type] = 'TR' /*FOR TRIGGER*/
Please let me know if we have similar options available to get the details for Sequence
objects
Advertisement
Answer
A SEQUENCE
doesn’t have the same type of definition as an object like a VIEW
or PROCEDURE
, however, you could generate your own:
CREATE SEQUENCE dbo.YourSEQUENCE START WITH 7 INCREMENT BY 4; GO SELECT NEXT VALUE FOR dbo.YourSEQUENCE; GO SELECT * FROM sys.sequences GO SELECT CONCAT(N'CREATE SEQUENCE ' + QUOTENAME(s.[name]) + N',' + QUOTENAME(sq.[name]),NCHAR(13) + NCHAR(10), N' START WITH ',CONVERT(int,sq.start_value), NCHAR(13) + NCHAR(10), N' INCREMENT BY ',CONVERT(int,sq.increment),N';') FROM sys.schemas s JOIN sys.sequences sq ON s.schema_id = sq.schema_id WHERE s.[name] = N'dbo' AND sq.[name] = N'yourSEQUENCE'; GO DROP SEQUENCE dbo.YourSEQUENCE;
If this is so you have a repository of all your definitions, that should already be in your Source Control Software.