Retrieve definition of Sequence object in SQL Server

Question

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

Sequences are stored in sys.sequences. See stackoverflow.com/questions/50859594/… for more info. — Thailo
– Thailo, Commented Dec 24, 2019 at 12:43

Thom A · Accepted Answer · 2019-12-24 12:40:32Z

3

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.

answered Dec 24, 2019 at 12:40

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Guzman Over a year ago

+1 for source control. Querying the catalog views is good for ad-hoc needs but I suggest SMO for general DDL scripting needs.

Akhileshkumar singh Over a year ago

@@user1941025...You should accept this answer ..If it's work for u...

HABO Over a year ago

@user1941025 If your sequences cycle, have minvalue or maxvalue, or are cached then you have a little more work to do. sys.sequences has the additional data you'll need to reconstruct those portions of the create sequnce statement. case expressions can be used to add the necessary code to the statements.

Akhileshkumar singh · Accepted Answer · 2019-12-24 12:47:12Z

Your Above Query is right..... i.e.'V' -- FOR VIEW 'P' -- FOR PROCEDURE 'TR' -- FOR TRIGGER

 SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition  
    FROM sys.sql_modules AS sm  
    JOIN sys.objects AS o ON sm.object_id = o.object_id  
    ORDER BY o.type;

Use this Query...you will get all the data in single set just refer type Column Name.

Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module.

The SQL Server Database Engine assumes that object_id is in the current database context. 

The collation of the object definition always matches that of the calling database context.

    OBJECT_DEFINITION applies to the following object types:

    C = Check constraint

    D = Default (constraint or stand-alone)

    P = SQL stored procedure

    FN = SQL scalar function

    R = Rule

    RF = Replication filter procedure

    TR = SQL trigger (schema-scoped DML trigger, or DDL trigger at either the database or server scope)

    IF = SQL inline table-valued function

    TF = SQL table-valued function

    V = View

For better info ...use this link...

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-modules-transact-sql?view=sql-server-ver15

Collectives™ on Stack Overflow

Retrieve definition of Sequence object in SQL Server

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related