본문 바로가기
IT/MS SQL

MS SQL CRUD 자동 생성 프로시저 쿼리

by heavenLake 2022. 7. 21.
반응형

 프로그램을 개발하다보면, 반복적으로 CRUD SQL문을 작성하는 경우가 많다.

시간을 어느정도 절약 할 수 있도록 공유된 프로시저를 이용하면 기본 SELECT, INSERT, UPDATE, DELETE 문을 만들어주기에 개발속도 및 개발 효율성을 증가 시킬 수 있다.  

 

-- EXEC [dbo].ZZZ.PROCEDURE_CREATOR '첫번째파라미터','두번째파라미터','세번째파라미터'

 

첫번째파라미터 => Database 테이블명

두번째파라미터 => 프로시저 생성자이름

세번째파라미터 => 프로시저에 대한 설명 공통문

 

CRUD 자동 스크립트

-- ============================================= 
-- AUTHOR:   ProSungsiler
-- CREATE DATE: 2019-06-17
-- DESCRIPTION: 프로시저 스크립트 자동생성 
--				@P_TABLE_NAME	: DB 테이블 명
--				@P_CREATOR		: 프로시저 생성자 이름
--				@P_DESCRIPTION	: 주석 및 프로시저 설명
-- ============================================= 
-- EXEC [dbo].ZZZ_PROCEDURE_CREATOR 'PR_EMPLOYEE','ProSungsiler','기본코드'
CREATE PROCEDURE [dbo].[ZZZ_PROCEDURE_CREATOR]
	  
	 @P_TABLE_NAME	VARCHAR(50)		
	,@P_CREATOR		VARCHAR(50)		
	,@P_DESCRIPTION	VARCHAR(200)	
AS  
BEGIN  

	
	DECLARE @INDEX INT

	-- ===================컬럼정보======================== 
	DECLARE @P_COLUMN_NAME						VARCHAR(256)
	DECLARE @P_IS_NULLABLE						VARCHAR(256)
	DECLARE @P_DATA_TYPE						VARCHAR(256)
	DECLARE @P_CHARACTER_MAXIMUM_LENGTH			INT
	DECLARE @P_NUMERIC_PRECISION				INT
	DECLARE @P_NUMERIC_SCALE					INT
	DECLARE @P_PK_YN							VARCHAR(1)
	DECLARE @ORD_NO								INT
	-- ===================컬럼정보========================
	
	--SELECT 
	--	 *
	--FROM INFORMATION_SCHEMA.COLUMNS
	--WHERE TABLE_NAME = @P_TABLE_NAME
	--ORDER BY ORDINAL_POSITION ASC

	

	/* =====================================================
	-- C) 기본 프로시저 생성 
	-- =================================================== */
	PRINT('GO')
	PRINT('-- ============================================= ')
	PRINT('-- AUTHOR: '+ @P_CREATOR)
	PRINT('-- CREATE DATE: ' + CONVERT(VARCHAR,GETDATE(),23))
	PRINT('-- DESCRIPTION: ' + @P_DESCRIPTION + ' 생성')
	PRINT('-- ============================================= ')
	PRINT('-- EXEC [USP_'+ @P_TABLE_NAME +'_C]')
	PRINT('CREATE PROCEDURE [dbo].[USP_'+ @P_TABLE_NAME +'_C]' + CHAR(13))
	
	-- ==================================================================
	-- ===================1. 테이블 파라미터 정의======================== 

		DECLARE MYCUR CURSOR FOR
		SELECT 
			 COLUMN_NAME				-- 컬럼이름
			,IS_NULLABLE				-- NULL값 여부
			,DATA_TYPE					-- 데이터형식
			,CHARACTER_MAXIMUM_LENGTH	-- 문자열 길이 
			,NUMERIC_PRECISION			-- 숫자 정수부분
			,NUMERIC_SCALE				-- 숫자 소수부분
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @P_TABLE_NAME
		ORDER BY ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
				IF(UPPER(@P_DATA_TYPE) = 'VARCHAR' OR UPPER(@P_DATA_TYPE) = 'CHAR')
				BEGIN
					-- 일반문자열
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					
				END
				ELSE IF
					 ( UPPER(@P_DATA_TYPE) = 'NUMERIC' 
					OR UPPER(@P_DATA_TYPE) = 'DECIMAL'
					 )
				BEGIN
					-- 숫자
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
				END
				ELSE
				BEGIN
					-- 그외
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
				END
			
			SET @INDEX = @INDEX + 1

			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR
			
	-- ===================1. 테이블 파라미터 정의======================== 
	-- ==================================================================

	PRINT(CHAR(13)+'AS')
	PRINT('BEGIN')
	PRINT(CHAR(13))
	
	-- ==================================================================
	-- ===================2. 테이블 컬럼 정의======================== 

	PRINT('		INSERT INTO ' + @P_TABLE_NAME)
	PRINT('		(')
	
	DECLARE MYCUR CURSOR FOR
	SELECT 
		 COLUMN_NAME				-- 컬럼이름
		,IS_NULLABLE				-- NULL값 여부
		,DATA_TYPE					-- 데이터형식
		,CHARACTER_MAXIMUM_LENGTH	-- 문자열 길이 
		,NUMERIC_PRECISION			-- 숫자 정수부분
		,NUMERIC_SCALE				-- 숫자 소수부분
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = @P_TABLE_NAME
	ORDER BY ORDINAL_POSITION ASC

	SET @INDEX = 0

	OPEN MYCUR

	FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE
	WHILE (@@FETCH_STATUS=0)
	BEGIN      
		
		IF(@INDEX = 0)
		BEGIN
			PRINT('			 '+ @P_COLUMN_NAME)
		END
		ELSE
		BEGIN
			PRINT('			,'+ @P_COLUMN_NAME)
		END
		
		SET @INDEX = @INDEX + 1

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE

	END

	CLOSE MYCUR

	DEALLOCATE MYCUR

	PRINT('		)')
	PRINT('		VALUES')
	PRINT('		(')
	
	DECLARE MYCUR CURSOR FOR
	SELECT 
		 COLUMN_NAME				-- 컬럼이름
		,IS_NULLABLE				-- NULL값 여부
		,DATA_TYPE					-- 데이터형식
		,CHARACTER_MAXIMUM_LENGTH	-- 문자열 길이 
		,NUMERIC_PRECISION			-- 숫자 정수부분
		,NUMERIC_SCALE				-- 숫자 소수부분
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = @P_TABLE_NAME
	ORDER BY ORDINAL_POSITION ASC

	SET @INDEX = 0

	OPEN MYCUR

	FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE
	WHILE (@@FETCH_STATUS=0)
	BEGIN      
		
		IF(@INDEX = 0)
		BEGIN
			PRINT('			 @P_'+ @P_COLUMN_NAME)
		END
		ELSE
		BEGIN
			PRINT('			,@P_'+ @P_COLUMN_NAME)
		END
		
		SET @INDEX = @INDEX + 1

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE

	END

	CLOSE MYCUR

	DEALLOCATE MYCUR


	PRINT('		)')
	PRINT(CHAR(13))
	
	-- ===================2. 테이블 컬럼 정의======================== 
	-- ==================================================================
	PRINT('END')







	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT('GO')
	/* =====================================================
	-- R) 기본 프로시저 조회 
	-- =================================================== */
	PRINT('-- ============================================= ')
	PRINT('-- AUTHOR: '+ @P_CREATOR)
	PRINT('-- CREATE DATE: ' + CONVERT(VARCHAR,GETDATE(),23))
	PRINT('-- DESCRIPTION: ' + @P_DESCRIPTION + '조회')
	PRINT('-- ============================================= ')
	PRINT('-- EXEC [USP_'+ @P_TABLE_NAME +'_R]')
	PRINT('CREATE PROCEDURE [dbo].[USP_'+ @P_TABLE_NAME +'_R]' + CHAR(13))

	PRINT(CHAR(13)+'AS')
	PRINT('BEGIN')
	PRINT(CHAR(13))

	PRINT('		SELECT')
	
	-- ==================================================================
	-- ===================1. 테이블 컬럼 정의======================== 

	DECLARE MYCUR CURSOR FOR
	SELECT 
		 COLUMN_NAME				-- 컬럼이름
		,IS_NULLABLE				-- NULL값 여부
		,DATA_TYPE					-- 데이터형식
		,CHARACTER_MAXIMUM_LENGTH	-- 문자열 길이 
		,NUMERIC_PRECISION			-- 숫자 정수부분
		,NUMERIC_SCALE				-- 숫자 소수부분
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = @P_TABLE_NAME
	ORDER BY ORDINAL_POSITION ASC

	SET @INDEX = 0

	OPEN MYCUR

	FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE
	WHILE (@@FETCH_STATUS=0)
	BEGIN      
		
		IF(@INDEX = 0)
		BEGIN
			PRINT('			 '+ @P_COLUMN_NAME)
		END
		ELSE
		BEGIN
			PRINT('			,'+ @P_COLUMN_NAME)
		END
		
		SET @INDEX = @INDEX + 1

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE

	END

	CLOSE MYCUR

	DEALLOCATE MYCUR

	-- ===================1. 테이블 컬럼 정의======================== 
	-- ==================================================================
	

	PRINT('		FROM ' + @P_TABLE_NAME) 


	PRINT(CHAR(13))
	PRINT('END')


	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT('GO')
	/* =====================================================
	-- U) 기본 프로시저 수정
	-- =================================================== */
	PRINT('-- ============================================= ')
	PRINT('-- AUTHOR: '+ @P_CREATOR)
	PRINT('-- CREATE DATE: ' + CONVERT(VARCHAR,GETDATE(),23))
	PRINT('-- DESCRIPTION: ' + @P_DESCRIPTION + '수정')
	PRINT('-- ============================================= ')
	PRINT('-- EXEC [USP_'+ @P_TABLE_NAME +'_U]')
	PRINT('CREATE PROCEDURE [dbo].[USP_'+ @P_TABLE_NAME +'_U]' + CHAR(13))

	-- ==================================================================
	-- ===================1. 테이블 파라미터 정의======================== 

		DECLARE MYCUR CURSOR FOR
		SELECT DISTINCT
			 A.COLUMN_NAME					-- 컬럼이름
			,A.IS_NULLABLE					-- NULL값 여부
			,A.DATA_TYPE					-- 데이터형식
			,A.CHARACTER_MAXIMUM_LENGTH		-- 문자열 길이 
			,A.NUMERIC_PRECISION			-- 숫자 정수부분
			,A.NUMERIC_SCALE				-- 숫자 소수부분
			,CASE WHEN ISNULL(B.COLUMN_NAME,'') = '' THEN 'N' ELSE 'Y' END	PK_YN	-- 기본키항목값을 가져오면 기본키
			,A.ORDINAL_POSITION
		FROM INFORMATION_SCHEMA.COLUMNS A
		LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		  ON B.TABLE_NAME = A.TABLE_NAME
		 AND B.COLUMN_NAME = A.COLUMN_NAME
		WHERE A.TABLE_NAME = @P_TABLE_NAME
		ORDER BY A.ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
				IF(UPPER(@P_DATA_TYPE) = 'VARCHAR' OR UPPER(@P_DATA_TYPE) = 'CHAR')
				BEGIN
					-- 일반문자열
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					
				END
				ELSE IF
					 ( UPPER(@P_DATA_TYPE) = 'NUMERIC' 
					OR UPPER(@P_DATA_TYPE) = 'DECIMAL'
					 )
				BEGIN
					-- 숫자
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
				END
				ELSE
				BEGIN
					-- 그외
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
				END
			
			SET @INDEX = @INDEX + 1

			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR
			
	-- ===================1. 테이블 파라미터 정의======================== 
	-- ==================================================================

	PRINT(CHAR(13)+'AS')
	PRINT('BEGIN')
	PRINT(CHAR(13))

	PRINT('		UPDATE ' + @P_TABLE_NAME)
	PRINT('		SET')
	-- ==================================================================
	-- ===================2. 테이블 업데이트 내역 정의===================
	 
	 DECLARE MYCUR CURSOR FOR
		SELECT DISTINCT
			 A.COLUMN_NAME					-- 컬럼이름
			,A.IS_NULLABLE					-- NULL값 여부
			,A.DATA_TYPE					-- 데이터형식
			,A.CHARACTER_MAXIMUM_LENGTH		-- 문자열 길이 
			,A.NUMERIC_PRECISION			-- 숫자 정수부분
			,A.NUMERIC_SCALE				-- 숫자 소수부분
			,CASE WHEN ISNULL(B.COLUMN_NAME,'') = '' THEN 'N' ELSE 'Y' END	PK_YN	-- 기본키항목값을 가져오면 기본키
			,A.ORDINAL_POSITION
		FROM INFORMATION_SCHEMA.COLUMNS A
		LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		  ON B.TABLE_NAME = A.TABLE_NAME
		 AND B.COLUMN_NAME = A.COLUMN_NAME
		WHERE A.TABLE_NAME = @P_TABLE_NAME
		ORDER BY A.ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
			
			IF(@P_PK_YN = 'N')
			BEGIN
				IF(@INDEX = 0)
				BEGIN
					PRINT('			 '+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				ELSE
				BEGIN
					PRINT('			,'+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				
				SET @INDEX = @INDEX + 1	
			END
			
			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR

	-- ===================2. 테이블 업데이트 내역 정의===================
	-- ================================================================== 

	
	-- ==================================================================
	-- ===================3. 테이블 업데이트 기본키 내역 정의===================
	
	DECLARE MYCUR CURSOR FOR
		SELECT DISTINCT
			 A.COLUMN_NAME					-- 컬럼이름
			,A.IS_NULLABLE					-- NULL값 여부
			,A.DATA_TYPE					-- 데이터형식
			,A.CHARACTER_MAXIMUM_LENGTH		-- 문자열 길이 
			,A.NUMERIC_PRECISION			-- 숫자 정수부분
			,A.NUMERIC_SCALE				-- 숫자 소수부분
			,CASE WHEN ISNULL(B.COLUMN_NAME,'') = '' THEN 'N' ELSE 'Y' END	PK_YN	-- 기본키항목값을 가져오면 기본키
			,A.ORDINAL_POSITION
		FROM INFORMATION_SCHEMA.COLUMNS A
		LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		  ON B.TABLE_NAME = A.TABLE_NAME
		 AND B.COLUMN_NAME = A.COLUMN_NAME
		WHERE A.TABLE_NAME = @P_TABLE_NAME
		ORDER BY A.ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
			
			IF(@P_PK_YN = 'Y')
			BEGIN
				IF(@INDEX = 0)
				BEGIN
					PRINT('		WHERE '+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				ELSE
				BEGIN
					PRINT('		  AND '+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				
				SET @INDEX = @INDEX + 1
					
			END
			
			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR

	-- ===================3. 테이블 업데이트 기본키 내역 정의===================
	-- =========================================================================

	PRINT(CHAR(13))
	PRINT('END')







	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT('GO')
	/* =====================================================
	-- D) 기본 프로시저 수정
	-- =================================================== */
	PRINT('-- ============================================= ')
	PRINT('-- AUTHOR: '+ @P_CREATOR)
	PRINT('-- CREATE DATE: ' + CONVERT(VARCHAR,GETDATE(),23))
	PRINT('-- DESCRIPTION: ' + @P_DESCRIPTION + '삭제')
	PRINT('-- ============================================= ')
	PRINT('-- EXEC [USP_'+ @P_TABLE_NAME +'_D]')
	PRINT('CREATE PROCEDURE [dbo].[USP_'+ @P_TABLE_NAME +'_D]' + CHAR(13))

	-- =========================================================================
	-- ===================1. 파라미터 내역 정의===================
	
	DECLARE MYCUR CURSOR FOR
		SELECT DISTINCT
			 A.COLUMN_NAME					-- 컬럼이름
			,A.IS_NULLABLE					-- NULL값 여부
			,A.DATA_TYPE					-- 데이터형식
			,A.CHARACTER_MAXIMUM_LENGTH		-- 문자열 길이 
			,A.NUMERIC_PRECISION			-- 숫자 정수부분
			,A.NUMERIC_SCALE				-- 숫자 소수부분
			,CASE WHEN ISNULL(B.COLUMN_NAME,'') = '' THEN 'N' ELSE 'Y' END	PK_YN	-- 기본키항목값을 가져오면 기본키
			,A.ORDINAL_POSITION
		FROM INFORMATION_SCHEMA.COLUMNS A
		LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		  ON B.TABLE_NAME = A.TABLE_NAME
		 AND B.COLUMN_NAME = A.COLUMN_NAME
		WHERE A.TABLE_NAME = @P_TABLE_NAME
		ORDER BY A.ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
			
			IF(@P_PK_YN = 'Y')
			BEGIN
				
				IF(UPPER(@P_DATA_TYPE) = 'VARCHAR' OR UPPER(@P_DATA_TYPE) = 'CHAR')
				BEGIN
					-- 일반문자열
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					
				END
				ELSE IF
					 ( UPPER(@P_DATA_TYPE) = 'NUMERIC' 
					OR UPPER(@P_DATA_TYPE) = 'DECIMAL'
					 )
				BEGIN
					-- 숫자
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
				END
				ELSE
				BEGIN
					-- 그외
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
				END
			
				SET @INDEX = @INDEX + 1

			END

			
			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR

	-- ===================1. 파라미터 내역 정의===================
	-- =========================================================================


	PRINT(CHAR(13)+'AS')
	PRINT('BEGIN')
	PRINT(CHAR(13))
	
	PRINT('		DELETE FROM ' + @P_TABLE_NAME)
	-- ==================================================================
	-- ===================2. 테이블 삭제 기본키 내역 정의===================
	
	DECLARE MYCUR CURSOR FOR
		SELECT DISTINCT
			 A.COLUMN_NAME					-- 컬럼이름
			,A.IS_NULLABLE					-- NULL값 여부
			,A.DATA_TYPE					-- 데이터형식
			,A.CHARACTER_MAXIMUM_LENGTH		-- 문자열 길이 
			,A.NUMERIC_PRECISION			-- 숫자 정수부분
			,A.NUMERIC_SCALE				-- 숫자 소수부분
			,CASE WHEN ISNULL(B.COLUMN_NAME,'') = '' THEN 'N' ELSE 'Y' END	PK_YN	-- 기본키항목값을 가져오면 기본키
			,A.ORDINAL_POSITION
		FROM INFORMATION_SCHEMA.COLUMNS A
		LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		  ON B.TABLE_NAME = A.TABLE_NAME
		 AND B.COLUMN_NAME = A.COLUMN_NAME
		WHERE A.TABLE_NAME = @P_TABLE_NAME
		ORDER BY A.ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
			
			IF(@P_PK_YN = 'Y')
			BEGIN
				IF(@INDEX = 0)
				BEGIN
					PRINT('		WHERE '+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				ELSE
				BEGIN
					PRINT('		  AND '+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				
				SET @INDEX = @INDEX + 1
					
			END
			
			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR

	-- ===================2. 테이블 삭제 기본키 내역 정의===================
	-- =========================================================================


	PRINT(CHAR(13))
	PRINT('END')


END

 

 

출처 : 

https://maelife.tistory.com/136 [고땡 세상만사:티스토리]

반응형

'IT > MS SQL' 카테고리의 다른 글

MSSQL WITH(NOLOCK) , SELECT시 잠금무시  (0) 2022.07.21
MSSQL 형변환 방법 CONVERT와 CAST  (1) 2022.07.21
mssql ssms 단축키 지정하기  (0) 2020.06.10
Collation 충돌 에러 해결하기  (0) 2020.06.10
mssql Lock 확인 및 대처  (0) 2020.06.05

관련글

댓글

티스토리툴바