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.

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!

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