해당 테이블에 Insert, Update, Delete 프로시저 자동생성

USE [UDB_SAMPLE]
GO
/****** Object:  StoredProcedure [dbo].[Sys_CreateProc]    Script Date: 08/15/2009 17:40:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROC [dbo].[Sys_CreateProc]
(
  @TableNm  VarChar(100) = null
  , @Owner Varchar(100) = 'dbo'
)
AS
SET XACT_ABORT ON
BEGIN

SET NOCOUNT ON

 If @TableNm Is Null Or @TableNm = '' Begin
  Print '테이블명을 입력해주세요'
  Return
 End

 -- 변수선언
 Declare @Sql_In  NVarchar(4000)
 Declare @Sql_In1 NVarchar(4000)
 Declare @Sql_In2 NVarchar(4000)
 Declare @Sql_Up  NVarchar(4000)
 Declare @Sql_Up1 NVarchar(4000)
 Declare @Sql_Up2 NVarchar(4000)
 Declare @Sql_Del NVarchar(4000)

 Declare @Sql_Declare1 NVarchar(4000)
 Declare @Sql_Declare1_1 NVarchar(4000)
 Declare @Sql_Declare1_2 NVarchar(4000)
 Declare @Sql_Declare1_3 NVarchar(4000)
 Declare @Sql_Declare1_4 NVarchar(4000)
 Declare @Gubun   char(3)

 Declare @ColumnName sql_variant 
 Declare @ColumnComment sql_variant 
 Declare @ColumnDataType sql_variant 
 Declare @ColumnDataLen sql_variant
 Declare @ColumnDataLen1 sql_variant
 Declare @ColumnDataLen2 sql_variant

 Declare @IsIdentity sql_variant 
 Declare @IsNullable sql_variant 
 Declare @Collation_name sql_variant 
 Declare @Definition sql_variant 
 Declare @IsPk Int
 Declare @IsOutPut Int
 Declare @Precision sql_variant 
 Declare @Scale  sql_variant

 Declare @InsertProc varchar(max)
 Declare @UpdateProc varchar(max)
 Declare @DeleteProc varchar(max)

 SET @Sql_In   = ''
 SET @Sql_In1  = ''
 SET @Sql_In2  = ''
 SET @Sql_Up   = ''
 SET @Sql_Up1  = ''
 SET @Sql_Up2  = ''
 SET @Sql_Del  = ''
 SET @Sql_Declare1 = ''

 -- 셋팅
 Declare myCursor INSENSITIVE cursor
 For 
  SELECT 
   c.name
   , type_name(user_type_id) As 'Type'
   , (
     case when (type_name(user_type_id) = 'varchar' or type_name(user_type_id) = 'nvarchar' or type_name(user_type_id) ='char' or type_name(user_type_id) ='nchar')
      then cast(max_length as varchar)
      when type_name(user_type_id) = 'decimal'  
       then cast([precision] as varchar) + ',' + cast(c.scale as varchar) 
     else ''
     end    
    ) As 'Length'
   , ( Case When Tmp.Name Is Null Then 0 Else 1 End ) As IsPk
   , c.is_identity
  FROM sys.columns c
   left outer join sys.extended_properties p on p.major_id = c.object_id and p.minor_id  = c.column_id and p.name = 'MS_Description' 
   Left outer Join
   (
   SELECT i.name 
   , col_name(i.object_id, c.column_id) As 'col_name'
   , i.object_id
   FROM sys.indexes i 
    inner join sys.index_columns c on i.index_id = c.index_id and c.object_id = i.object_id 
   WHERE i.name Like 'pk%' 
   And i.object_id  = Object_ID( @Owner + '.' + @TableNm )
   ) As Tmp On Tmp.object_id = c.object_id And Tmp.col_name = c.name 
  WHERE c.Object_id = Object_ID( @Owner + '.' + @TableNm )
  order by c.column_id

 Open myCursor
 -- Fetch Next FROM myCursor Into @ColumnName , @ColumnDataType , @ColumnDataLen , @IsIdentity , @IsNullable , @Collation_name , @Definition , @IsPk , @Precision , @Scale
 Fetch Next FROM myCursor Into @ColumnName , @ColumnDataType , @ColumnDataLen , @IsPk , @IsIdentity

  while (@@Fetch_Status=0)
   Begin
    -- INSERT 셋팅
    IF @IsIdentity <> 1 And UPPer(Convert(Varchar,@ColumnName)) <> 'DT_INSYSDATE' And UPPer(Convert(Varchar,@ColumnName)) <> 'DT_MDSYSDATE' Begin
     If @Sql_In1 = '' Begin
      SET @Sql_In1 = '  '
     End
     Else Begin
      SET @Sql_In1 = @Sql_In1 + char(13) + char(10)+ '  ,'
     End
     
     If @Sql_In2 = '' Begin
      SET @Sql_In2 = '  '
     End
     Else Begin
      SET @Sql_In2 = @Sql_In2 + char(13) + char(10)+ '  ,'
     End
     
     SET @Sql_In1 = @Sql_In1 + Convert(Varchar,@ColumnName)
     SET @Sql_In2 = @Sql_In2 + '@' + Convert(Varchar,@ColumnName)
    End
    
    -- UPDATE 셋팅
    IF @IsIdentity <> 1 And Convert(Varchar,@ColumnName) <> 'DT_INSYSDATE' And Convert(Varchar,@ColumnName) <> 'DT_MDSYSDATE' Begin
     If @Sql_Up1 = '' Begin
      SET @Sql_Up1 = ' '
     End
     Else Begin
      SET @Sql_Up1 = @Sql_Up1 + char(13) + char(10)+ '  ,'
     End

     SET @Sql_Up1 = @Sql_Up1 + Convert(Varchar,@ColumnName) + ' = @' + Convert(Varchar,@ColumnName)
    End
    
    -- PK 셋팅
    If @IsPk = 1 Begin
     If @Sql_Up2 != '' Begin
      SET @Sql_Up2 = @Sql_Up2 + char(13) + char(10)+ ','
     End
     SET @Sql_Up2 = @Sql_Up2 + Convert(Varchar,@ColumnName) + ' = @' + Convert(Varchar,@ColumnName)

    End
    
    -- 변수선언 셋팅
    IF @IsIdentity <> 1 And UPPer(Convert(Varchar,@ColumnName)) <> 'DT_INSYSDATE' And UPPer(Convert(Varchar,@ColumnName)) <> 'DT_MDSYSDATE' Begin
     If @Sql_Declare1 = '' Begin
      SET @Sql_Declare1 = ' '
     End
     Else Begin
      SET @Sql_Declare1 = @Sql_Declare1 + char(13) + char(10)+ ' ,'
     End
     
     IF Convert(Varchar,@ColumnDataLen) = '' Begin
      SET @Sql_Declare1 = @Sql_Declare1 + '@' + Convert(Varchar,@ColumnName) + ' ' + Convert(Varchar,@ColumnDataType)
     End
     Else Begin
      SET @Sql_Declare1 = @Sql_Declare1 + '@' + Convert(Varchar,@ColumnName) + ' ' + Convert(Varchar,@ColumnDataType) + '(' + Convert(Varchar,@ColumnDataLen) + ')'
     End
    End

    Fetch Next FROM myCursor Into @ColumnName , @ColumnDataType , @ColumnDataLen  , @IsPk , @IsIdentity
   End
   
 Close myCursor
 DeAllocate myCursor

 
 -- INSERT 셋팅
 SET @Sql_In = ' INSERT INTO ' + @TableNm + char(13) + char(10) 
 SET @Sql_In = @Sql_In + ' (' + char(13) + char(10) 
 SET @Sql_In = @Sql_In + @Sql_In1 + char(13) + char(10) 
 SET @Sql_In = @Sql_In + ' )' + char(13) + char(10) 
 SET @Sql_In = @Sql_In + ' VALUES' + char(13) + char(10) 
 SET @Sql_In = @Sql_In + ' (' + char(13) + char(10) 
 SET @Sql_In = @Sql_In + @Sql_In2 + char(13) + char(10) 
 SET @Sql_In = @Sql_In + ' )' + char(13) + char(10) 
 
 -- UPDATE 셋팅
 SET @Sql_Up = ' UPDATE ' + @TableNm + ' SET ' + char(13) + char(10) 
 SET @Sql_Up = @Sql_Up + ' '+@Sql_Up1 + char(13) + char(10) 
 SET @Sql_Up = @Sql_Up + ' WHERE ' + Replace(@Sql_Up2,',','  And ') + char(13) + char(10)

 -- DELETE 셋팅
 SET @Sql_Del = ' DELETE FROM ' + @TableNm + char(13) + char(10) 
 SET @Sql_Del = @Sql_Del + ' WHERE ' + Replace(@Sql_Up2,',','  And ') + char(13) + char(10) 
 
 
 -- Proc 기본셋팅
 SET @Sql_Declare1_1 = 'USE [UDB_SAMPLE]' + char(13) + char(10)
 SET @Sql_Declare1_1 = @Sql_Declare1_1 + 'GO' + char(13) + char(10)
 
 SET @Sql_Declare1_2 = 'SET ANSI_NULLS ON' + char(13) + char(10)
 SET @Sql_Declare1_2 = @Sql_Declare1_2 + 'GO' + char(13) + char(10)
 SET @Sql_Declare1_2 = @Sql_Declare1_2 + 'SET QUOTED_IDENTIFIER ON' + char(13) + char(10)
 SET @Sql_Declare1_2 = @Sql_Declare1_2 + 'GO' + char(13) + char(10)
 SET @Sql_Declare1_2 = @Sql_Declare1_2 + char(13) + char(10)
 
 SET @Sql_Declare1_3 = @Sql_Declare1 + char(13) + char(10)
 SET @Sql_Declare1_3 = @Sql_Declare1_3 + 'AS' + char(13) + char(10)
 SET @Sql_Declare1_3 = @Sql_Declare1_3 + 'SET XACT_ABORT ON' + char(13) + char(10)
 SET @Sql_Declare1_3 = @Sql_Declare1_3 + char(13) + char(10)
 SET @Sql_Declare1_3 = @Sql_Declare1_3 + 'BEGIN TRY' + char(13) + char(10)
 
 SET @Sql_Declare1_4 = 'END TRY' + char(13) + char(10)
 SET @Sql_Declare1_4 = @Sql_Declare1_4 + char(13) + char(10)
 SET @Sql_Declare1_4 = @Sql_Declare1_4 + 'BEGIN CATCH' + char(13) + char(10)
 SET @Sql_Declare1_4 = @Sql_Declare1_4 + ' DECLARE @ErrorMessage NVARCHAR(4000)' + char(13) + char(10)
 SET @Sql_Declare1_4 = @Sql_Declare1_4 + ' DECLARE @ErrorSeverity INT' + char(13) + char(10)
 SET @Sql_Declare1_4 = @Sql_Declare1_4 + ' DECLARE @ErrorState INT' + char(13) + char(10)
 SET @Sql_Declare1_4 = @Sql_Declare1_4 + '' + char(13) + char(10)
 SET @Sql_Declare1_4 = @Sql_Declare1_4 + '    EXEC USP_ADMINLogError' + char(13) + char(10)
 SET @Sql_Declare1_4 = @Sql_Declare1_4 + '    SELECT @ErrorMessage = ERROR_MESSAGE(),' + char(13) + char(10)
 SET @Sql_Declare1_4 = @Sql_Declare1_4 + '           @ErrorSeverity = ERROR_SEVERITY(),' + char(13) + char(10)
 SET @Sql_Declare1_4 = @Sql_Declare1_4 + '           @ErrorState = ERROR_STATE()' + char(13) + char(10)
 SET @Sql_Declare1_4 = @Sql_Declare1_4 + '' + char(13) + char(10)
 SET @Sql_Declare1_4 = @Sql_Declare1_4 + '    RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState )' + char(13) + char(10)
 SET @Sql_Declare1_4 = @Sql_Declare1_4 + 'END CATCH' + char(13) + char(10)
 SET @Sql_Declare1_4 = @Sql_Declare1_4 + 'GO' + char(13) + char(10)
 SET @Sql_Declare1_4 = @Sql_Declare1_4 + char(13) + char(10)
 
 Set @InsertProc = ''
 Set @UpdateProc = ''
 Set @DeleteProc = ''
 
 -- Insert Proc 생성
 SET @Gubun = 'INS'
 Set @InsertProc = @Sql_Declare1_1
 Set @InsertProc = @InsertProc + '/****** Object:  StoredProcedure ['+@Owner+'].[USP_'+@TableNm+'_'+@Gubun+']    Script Date: '+Convert(varchar(30),GETDATE(),101)+' ******/' + char(13) + char(10)
 Set @InsertProc = @InsertProc + @Sql_Declare1_2
 Set @InsertProc = @InsertProc + 'CREATE PROCEDURE ['+@Owner+'].[USP_'+@TableNm+'_'+@Gubun+']' + char(13) + char(10)
 Set @InsertProc = @InsertProc + @Sql_Declare1_3
 Set @InsertProc = @InsertProc + @Sql_In
 Set @InsertProc = @InsertProc + @Sql_Declare1_4
 
 -- Update Proc 생성
 SET @Gubun = 'UPD'
 Set @UpdateProc = @Sql_Declare1_1
 Set @UpdateProc = @UpdateProc + '/****** Object:  StoredProcedure ['+@Owner+'].[USP_'+@TableNm+'_'+@Gubun+']    Script Date: '+Convert(varchar(30),GETDATE(),101)+' ******/' + char(13) + char(10)
 Set @UpdateProc = @UpdateProc + @Sql_Declare1_2
 Set @UpdateProc = @UpdateProc + 'CREATE PROCEDURE ['+@Owner+'].[USP_'+@TableNm+'_'+@Gubun+']' + char(13) + char(10)
 Set @UpdateProc = @UpdateProc + @Sql_Declare1_3
 Set @UpdateProc = @UpdateProc + @Sql_Up
 Set @UpdateProc = @UpdateProc + @Sql_Declare1_4
 
 -- DELETE Proc 생성
 SET @Gubun = 'DEL'
 Set @DeleteProc = @Sql_Declare1_1
 Set @DeleteProc = @DeleteProc + '/****** Object:  StoredProcedure ['+@Owner+'].[USP_'+@TableNm+'_'+@Gubun+']    Script Date: '+Convert(varchar(30),GETDATE(),101)+' ******/' + char(13) + char(10)
 Set @DeleteProc = @DeleteProc + @Sql_Declare1_2
 Set @DeleteProc = @DeleteProc + 'CREATE PROCEDURE ['+@Owner+'].[USP_'+@TableNm+'_'+@Gubun+']' + char(13) + char(10)
 Set @DeleteProc = @DeleteProc + @Sql_Declare1_3
 Set @DeleteProc = @DeleteProc + @Sql_Del
 Set @DeleteProc = @DeleteProc + @Sql_Declare1_4
 
 --Exec (@InsertProc)
 --Exec (@UpdateProc)
 --Exec (@DeleteProc)
  
 print (@InsertProc)
 print (@UpdateProc)
 print (@DeleteProc)

 SET NOCOUNT OFF
 End