How can I determine if a Sequence exist in SQL Server 2012?

Question

I need to create a SQL script to determine if a sequence exists in a SQL Server 2012 database. I am familiar with process for determine if a stored procedure exist but not sequence. Thank you.

Mold

Answer 1

The script to determine whether or not a Sequence exists in SQL Server 2012 is very similar to checking for Stored Procedures. Consider the following code that checks to see if a Stored Procedure exists:

SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[SProc_Name]') AND type IN (N'P', N'PC')

The values of 'P' and 'PC' for the type specify the type of the sys.object is a SQL Stored Procedure or a Assembly (CLR) stored-procedure. To check for a sequence, you just need to change it to 'SO' which indicates it is a Sequence Object:

SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Sequence_Name]') AND type = 'SO'

For example, if you want to create a Sequence if it doesn't exist, you could use the following code:

IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sequence_Name]') AND type = 'SO')
CREATE SEQUENCE [dbo].[Sequence_Name] 
    AS [bigint]
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE  3 
GO

I hope this helps!

Dyna

As of SQL Server 2005, it's preferable to use the more focused catalog views, like sys.sequences rather than querying the "catch-all" sys.objects and having to remember the necessary type for your object in question .... – Jellyfish

@Jellyfish is correct that it's easier to remember the "more focused catalog views." Out of habit, I still use sys.objects type, but I am now considering making the transition. – Industrialize

@StevenFerrer Based on the article you provided, "Approach 4" indicates that "sys.sysobjects...will be deprecated" and not sys.objects: "Microsoft is suggesting to use the catalog view sys.objects instead of sys.sysobjects system..." – Industrialize

@myidealab, good catch! I've deleted my comment since it doesn't really add value to this answer. haha. thanks very much! – Cocoa

Answer 2

By checking data in sys.sequences table:

select *
from sys.sequences
where object_id = object_id('schema_name.sequence_name')

actually that if you're sure that there's no object other than sequence with name equals 'schema_name.sequence_name', you could just check object_id('schema_name.sequence_name') is not null

sql fiddle demo

Scarificator

Answer 3

This is another shortcut version of Tim S:

IF OBJECT_ID('schema_name.sequence_name', 'SO') IS NOT NULL 
BEGIN
    DROP SEQUENCE schema_name.sequence_name
END

Biotite

Answer 4

Try this. This will list all of the sequences for a given database.

SELECT
seq.name AS [Sequence Name],
seq.object_id AS [Object ID],
seq.create_date AS [Creation Date],
seq.modify_date AS [Last Modified Date],
SCHEMA_NAME(seq.schema_id) AS [Schema],
CAST(seq.precision AS int) AS [Numeric Precision],
CAST(seq.scale AS int) AS [Numeric Scale],
ISNULL(seq.start_value,N'''') AS [Start Value],
ISNULL(seq.increment,N'''') AS [Increment Value],
ISNULL(seq.minimum_value,N'''') AS [Min Value],
ISNULL(seq.maximum_value,N'''') AS [Max Value],
CAST(seq.is_cycling AS bit) AS [Is Cycle Enabled],
ISNULL(seq.cache_size,0) AS [Cache Size],
ISNULL(seq.current_value,N'''') AS [Current Value]
FROM sys.sequences AS seq

Trawick

Answer 5

I'm using SQL Server 17.4 (Latest version of MS SQL Sever) and this SQL script worked with me. This SQL script, for example, creates the sequence if not exists.

IF NOT EXISTS
(
SELECT [name]
FROM sys.sequences
WHERE [name] = 'seq_businessTripRequest'
)
BEGIN

CREATE SEQUENCE [dbo].[seq_businessTripRequest]
AS [BIGINT]
START WITH 1
INCREMENT BY 1
MINVALUE-9223372036854775808
MAXVALUE 9223372036854775807
CACHE;

END;

Sheik

Answer 6

How about INFORMATION_SCHEMA.SEQUENCES ?

IF NOT EXISTS
(
    SELECT SEQUENCE_SCHEMA, SEQUENCE_NAME, DATA_TYPE
    FROM INFORMATION_SCHEMA.SEQUENCES 
    WHERE SEQUENCE_SCHEMA = 'dbo' 
    AND SEQUENCE_NAME = 'CountBy' 
)
BEGIN
    EXECUTE('CREATE SEQUENCE dbo.CountBy START WITH 1 INCREMENT BY 1; '); 
END 

Should INFORMATION_SCHEMA.SEQUENCES not exist, why not create it ?

CREATE VIEW INFORMATION_SCHEMA.SEQUENCES AS 
SELECT 
     DB_NAME() AS SEQUENCE_CATALOG 
    ,SCHEMA_NAME(s.schema_id) AS SEQUENCE_SCHEMA 
    ,s.name AS SEQUENCE_NAME 
    ,ISNULL(TYPE_NAME(s.system_type_id), t.name) AS DATA_TYPE 
    ,s.precision AS NUMERIC_PRECISION 

    ,CONVERT
    ( 
        smallint 
        ,CASE  -- int/money/decimal/numeric 
            WHEN s.system_type_id IN (48, 52, 56, 60, 106, 108, 122, 127) THEN 10 
            WHEN s.system_type_id IN (59, 62) THEN 2 
         END 
     ) AS NUMERIC_PRECISION_RADIX 
            
    ,ODBCSCALE(s.system_type_id, s.scale) AS NUMERIC_SCALE -- for real/float
     
    ,s.start_value AS START_VALUE
    ,s.minimum_value AS MINIMUM_VALUE
    ,s.maximum_value AS MAXIMUM_VALUE
    ,s.increment AS INCREMENT
    ,s.is_cycling AS CYCLE_OPTION
    ,t.name AS DECLARED_DATA_TYPE
    ,t.precision AS DECLARED_NUMERIC_PRECISION
    ,t.scale AS DECLARED_NUMERIC_SCALE
FROM sys.sequences AS s
INNER JOIN sys.types t ON s.user_type_id = t.user_type_id 

Fitzger