Snowflake Create Sequence - it uses two cursors one for schema name and another for table name
- Kunal Ranpura
- Mar 8, 2023
- 1 min read
SET ANSI_NULLS ON;
SET ARITHABORT ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
Declare @SEQUENCE_SCHEMA varchar(max);
Declare @SEQUENCE_NAME varchar(max);
DECLARE @command nvarchar(4000);
DECLARE SEQUENCE_SCHEMA CURSOR FOR SELECT DISTINCT [SEQUENCE_SCHEMA] FROM [clone].[dbo].[table_seq_curated] where SEQUENCE_SCHEMA <>'INFORMATION_SCHEMA' ORDER BY SEQUENCE_SCHEMA;
open SEQUENCE_SCHEMA;
fetch next from SEQUENCE_SCHEMA into @SEQUENCE_SCHEMA;
while @@FETCH_STATUS = 0
Begin
DECLARE SEQUENCE_NAME CURSOR FOR SELECT [SEQUENCE_NAME] FROM [clone]. [dbo].[table_seq_curated] WHERE SEQUENCE_SCHEMA=@SEQUENCE_SCHEMA ORDER BY SEQUENCE_NAME;
open SEQUENCE_NAME;
fetch next from SEQUENCE_NAME into @SEQUENCE_NAME;
while @@FETCH_STATUS = 0
BEGIN
Set @command = N'create or replace sequence PRE_PROD_CURATED.'+@SEQUENCE_SCHEMA+'.'+@SEQUENCE_NAME+' start with 10502977 increment by 1;';
print @command
--execute (@command);
fetch next from SEQUENCE_NAME into @SEQUENCE_NAME
END
CLOSE SEQUENCE_NAME;
DEALLOCATE SEQUENCE_NAME;
fetch next from SEQUENCE_SCHEMA into @SEQUENCE_SCHEMA;
END
close SEQUENCE_SCHEMA;
deallocate SEQUENCE_SCHEMA;
Comments