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.
The script for determining 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!