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 https://stackoverflow.com/questions/50859594/export-database-as-scripts-sequences-not-set-correctly for more info. — Thailo, Dec 24 '19 at 12:43

score 3 · Accepted Answer · answered Dec 24 '19 at 12:40

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 '19 at 12:40

Thom A

88,727
11
45
75

1

+1 for source control. Querying the catalog views is good for ad-hoc needs but I suggest [SMO](https://learn.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/sql-server-management-objects-smo-programming-guide) for general DDL scripting needs. – Dan Guzman Dec 24 '19 at 13:02
@@user1941025...You should accept this answer ..If it's work for u... – THE LIFE-TIME LEARNER Dec 24 '19 at 13:53
1

@user1941025 If your sequences `cycle`, have `minvalue` or `maxvalue`, or are `cached` then you have a little more work to do. [`sys.sequences`](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sequences-transact-sql?view=sql-server-ver15) has the additional data you'll need to reconstruct those portions of the [`create sequnce`](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15) statement. `case` expressions can be used to add the necessary code to the statements. – HABO Dec 24 '19 at 14:20

score 1 · Answer 2 · answered Dec 24 '19 at 12:47

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

Retrieve definition of Sequence object in SQL Server

2 Answers2