我需要创建一个 SQL 脚本,以确定 SQL Server 2012 数据库中是否存在一个序列。我熟悉确定存储过程是否存在的过程,但不熟悉序列。谢谢。
我需要创建一个 SQL 脚本,以确定 SQL Server 2012 数据库中是否存在一个序列。我熟悉确定存储过程是否存在的过程,但不熟悉序列。谢谢。
确定 SQL Server 2012 中是否存在序列的脚本与检查存储过程非常相似。请考虑以下代码,该代码用于检查是否存在存储过程:
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[SProc_Name]') AND type IN (N'P', N'PC')
'P'和'PC'的值指定sys.object的类型是SQL存储过程还是程序集(CLR)存储过程。要检查一个序列,只需将其更改为'SO',表示它是一个序列对象:
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Sequence_Name]') AND type = 'SO'
例如,如果您想在不存在Sequence的情况下创建一个Sequence,您可以使用以下代码: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
我希望这可以帮到你!
通过检查 sys.sequences 表中的数据:
select *
from sys.sequences
where object_id = object_id('schema_name.sequence_name')
实际上,如果你确信除了名称为'schema_name.sequence_name'
的序列外没有其他对象,那么你可以只检查object_id('schema_name.sequence_name') is not null
IF OBJECT_ID('schema_name.sequence_name', 'SO') IS NOT NULL
BEGIN
DROP SEQUENCE schema_name.sequence_name
END
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
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;
sys.sequences
,而不是查询“全能型”的sys.objects
并记住所需对象的type
。 - marc_ssys.sysobjects
将被弃用”,而不是sys.objects
:“Microsoft建议使用目录视图sys.objects
代替sys.sysobjects
系统…”。 - datalifenyc