비슷한 테이블 매크로 생성

USE TESTDB
GO
USE TESTDB
GO
CREATE TABLE [dbo].[TBL](                 -- 테이블 정보를 저장할 테이블 만들기
 [HDTBL] [varchar](30) NULL,
 [DTLTBL] [varchar](30) NULL,
 [CCOUNT] [smallint] NULL,
 [FILEGROUP] [varchar](10) NULL
) ON [PRIMARY]
GO
-- 테이블 정보 입력
INSERT [dbo].[TBL] ([HDTBL], [DTLTBL], [CCOUNT], [FILEGROUP]) VALUES (N'EQ_AA_HD', N'EQ_CA01_DTL', 10, N'FGC')
INSERT [dbo].[TBL] ([HDTBL], [DTLTBL], [CCOUNT], [FILEGROUP]) VALUES (N'EQ_BB_HD', N'EQ_CA02_DTL', 35, N'FGC')
INSERT [dbo].[TBL] ([HDTBL], [DTLTBL], [CCOUNT], [FILEGROUP]) VALUES (N'EQ_CC_HD', N'EQ_CA03_DTL', 10, N'FGC')
INSERT [dbo].[TBL] ([HDTBL], [DTLTBL], [CCOUNT], [FILEGROUP]) VALUES (N'EQ_DD_HD', N'EQ_CA04_DTL', 5, N'FGC')
INSERT [dbo].[TBL] ([HDTBL], [DTLTBL], [CCOUNT], [FILEGROUP]) VALUES (N'EQ_EE_HD', N'EQ_CA05_DTL', 10, N'FGC')
GO

-- 데이터베이스 스키마 생성
DECLARE TBLCUR CURSOR
FOR SELECT * FROM TBL

OPEN TBLCUR

DECLARE @HD VARCHAR(30), @DTL VARCHAR(30), @ROW INT
DECLARE @HDTBL VARCHAR(5000), @DTLTBL VARCHAR(MAX), @FOREIGN VARCHAR (1000), @INDEX1 VARCHAR(1000),@INDEX2 VARCHAR(1000), @INDEX3 VARCHAR(1000), @FG VARCHAR(10)

SET @HD = ''
SET @DTL = ''
SET @FOREIGN = ''
SET @ROW = 0
SET @INDEX1 = ''
SET @INDEX2 = ''
SET @INDEX3 = ''
SET @FG = ''

FETCH NEXT FROM TBLCUR INTO @HD, @DTL, @ROW, @FG

WHILE @@FETCH_STATUS = 0
  BEGIN
 IF @ROW = 35
   BEGIN
    SET @HDTBL = 'CREATE TABLE ' + @HD + '(
      [SEQ] [BIGINT] IDENTITY(1,1) NOT NULL,
      [EQPT] [CHAR](10) NULL,
      [CB] [CHAR](1) NULL,
      [CC] [CHAR](1) NULL,
      [CD] [DATETIME] NULL,
      [CE] [VARCHAR](30) NULL,
      [CG] [VARCHAR](10) NULL,
      [LOTID] [VARCHAR](20) NULL
     PRIMARY KEY NONCLUSTERED
     ( [SEQ] ASC )) '
     
  SET @INDEX1 = 'CREATE CLUSTERED INDEX IX_' + @HD + 'TIME ON ' + @HD + '(TIME)'
  SET @INDEX2 = 'CREATE INDEX IX_' + @HD + 'EQPT_LOTID ON ' + @HD + '(EQPT, LOTID)'
  
  SET @DTLTBL = 'CREATE TABLE ' + @DTL + '(
    SEQ        BIGINT            NOT NULL,
    VAL001     varchar(20)    NULL,
    VAL002     varchar(20)    NULL,
    VAL003     varchar(20)    NULL,
    VAL004     varchar(20)    NULL,
    VAL005     varchar(20)    NULL,
    VAL006     varchar(20)    NULL,
    VAL007     varchar(20)    NULL,
    VAL008     varchar(20)    NULL,
    VAL009     varchar(20)    NULL,
    VAL010     varchar(20)    NULL,
    VAL011     varchar(20)    NULL,
    VAL012     varchar(20)    NULL,
    VAL013     varchar(20)    NULL,
    VAL014     varchar(20)    NULL,
    VAL015     varchar(20)    NULL,
    VAL016     varchar(20)    NULL,
    VAL017     varchar(20)    NULL,
    VAL018     varchar(20)    NULL,
    VAL019     varchar(20)    NULL,
    VAL020     varchar(20)    NULL,
    VAL021     varchar(20)    NULL,
    VAL022     varchar(20)    NULL,
    VAL023     varchar(20)    NULL,
    VAL024     varchar(20)    NULL,
    VAL025     varchar(20)    NULL,
    VAL026     varchar(20)    NULL,
    VAL027     varchar(20)    NULL,
    VAL028     varchar(20)    NULL,
    VAL029     varchar(20)    NULL,
    VAL030     varchar(20)    NULL,
    VAL031     varchar(20)    NULL,
    VAL032     varchar(20)    NULL,
    VAL033     varchar(20)    NULL,
    VAL034     varchar(20)    NULL,
    VAL035     varchar(20)    NULL

    [TIME] [datetime] NULL
    )'
    SET @INDEX3 = 'CREATE INDEX IX_'+ @DTL + ' ON ' + @DTL + '(SEQ)'
    
   SET @FOREIGN = 'ALTER TABLE ' + @DTL + ' ADD  CONSTRAINT FK_' + @HD + '_' + @DTL + ' FOREIGN KEY([SEQ])
    REFERENCES ' + @HD + ' ([SEQ])'
   END
  
  
 IF @ROW = 10
   BEGIN
    SET @HDTBL = 'CREATE TABLE ' + @HD + '(
      [SEQ] [BIGINT] IDENTITY(1,1) NOT NULL,
      [EQPT] [CHAR](10) NULL,
      [CA] [CHAR](1) NULL,
      [CB] [CHAR](1) NULL,
      [CC] [DATETIME] NULL,
      [CD] [VARCHAR](30) NULL,
      [CE] [VARCHAR](10) NULL,
      [LOTID] [VARCHAR](20) NULL

     PRIMARY KEY NONCLUSTERED
     ( [SEQ] ASC )) '

   SET @INDEX1 = 'CREATE CLUSTERED INDEX IX_' + @HD + 'TIME ON ' + @HD + '(TIME)'
   SET @INDEX2 = 'CREATE INDEX IX_' + @HD + 'EQPT_LOTID ON ' + @HD + '(EQPT, LOTID)'
   
   SET @DTLTBL = 'CREATE TABLE ' + @DTL + '(
    SEQ        BIGINT            NOT NULL,
    VAL001     varchar(20)    NULL,
    VAL002     varchar(20)    NULL,
    VAL003     varchar(20)    NULL,
    VAL004     varchar(20)    NULL,
    VAL005     varchar(20)    NULL,
    VAL006     varchar(20)    NULL,
    VAL007     varchar(20)    NULL,
    VAL008     varchar(20)    NULL,
    VAL009     varchar(20)    NULL,
    VAL010     varchar(20)    NULL

    [TIME] [datetime] NULL
    )'
    
    SET @INDEX3 = 'CREATE INDEX IX_'+ @DTL + ' ON ' + @DTL + '(SEQ)'
    
    SET @FOREIGN = 'ALTER TABLE ' + @DTL + ' ADD  CONSTRAINT FK_' + @HD + '_' + @DTL + ' FOREIGN KEY([SEQ])
    REFERENCES ' + @HD + ' ([SEQ])'
   END
  
  IF @ROW = 5
   BEGIN
    SET @HDTBL = 'CREATE TABLE ' + @HD + '(
      [SEQ] [BIGINT] IDENTITY(1,1) NOT NULL,
      [EQPT] [CHAR](10) NULL,
      [CA] [CHAR](1) NULL,
      [CB] [CHAR](1) NULL,
      [CC] [DATETIME] NULL,
      [CD] [VARCHAR](30) NULL,
      [CE] [VARCHAR](10) NULL,
      [LOTID] [VARCHAR](20) NULL
     PRIMARY KEY NONCLUSTERED
     ( [SEQ] ASC )) '
     
  SET @INDEX1 = 'CREATE CLUSTERED INDEX IX_' + @HD + 'TIME ON ' + @HD + '(TIME)'
  SET @INDEX2 = 'CREATE INDEX IX_' + @HD + 'EQPT_LOTID ON ' + @HD + '(EQPT, LOTID)'

  SET @DTLTBL = 'CREATE TABLE ' + @DTL + '(
    SEQ        BIGINT            NOT NULL,
    VAL001     varchar(20)    NULL,
    VAL002     varchar(20)    NULL,
    VAL003     varchar(20)    NULL,
    VAL004     varchar(20)    NULL,
    VAL005     varchar(20)    NULL
    [TIME] [datetime] NULL
    )'
    SET @INDEX3 = 'CREATE INDEX IX_'+ @DTL + ' ON ' + @DTL + '(SEQ)'
    
    SET @FOREIGN = 'ALTER TABLE ' + @DTL + ' ADD  CONSTRAINT FK_' + @HD + '_' + @DTL + ' FOREIGN KEY([SEQ])
    REFERENCES ' + @HD + ' ([SEQ])'
   END
  
  
   -- PRINT @DTLTBL + ' ON ' + @FG
   -- PRINT @HDTBL + ' ON ' + @FG
   -- PRINT @INDEX1 + ' ON ' + @FG
   -- PRINT @INDEX2 + ' ON ' + @FG
   
  EXEC (@DTLTBL + ' ON ' + @FG)
  EXEC (@HDTBL + ' ON ' + @FG)
  EXEC (@INDEX1 + ' ON ' + @FG)
  EXEC (@INDEX2 + ' ON ' + @FG)
  EXEC (@INDEX3 + ' ON ' + @FG)
  EXEC (@FOREIGN) 
  
  SET @HDTBL = ''
  SET @DTLTBL = ''
  SET @FOREIGN = ''

  FETCH NEXT FROM TBLCUR INTO @HD, @DTL, @ROW, @FG
  
  END
 
  CLOSE TBLCUR
 
  DEALLOCATE TBLCUR
 GO