[mssql] 테이블 컬럼으로 DECLARE 문 만들기

USE {{DB NAME}};

SELECT 'DECLARE @Kr_' + COLUMN_NAME + ' ' + UPPER(DATA_TYPE) 
	+ ( CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND CHARACTER_MAXIMUM_LENGTH > 0 THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' ELSE '' END ) 
	+ ' = NULL'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{{TABLE NAME}}'

결과

DECLARE @Kr_address NVARCHAR(200) = NULL
DECLARE @Kr_detailAddress NVARCHAR(200) = NULL
DECLARE @Kr_geoLocation GEOGRAPHY = NULL
DECLARE @Kr_complexNo INT = NULL
DECLARE @Kr_complexHscpNo INT = NULL
DECLARE @Kr_complexName NVARCHAR(50) = NULL
DECLARE @Kr_complexBuildingNo NVARCHAR(50) = NULL
DECLARE @Kr_complexPtpNo INT = NULL
DECLARE @Kr_complexPtpNm VARCHAR(20) = NULL
DECLARE @Kr_saleAmount MONEY = NULL
DECLARE @Kr_accessRoadDistance INT = NULL
DECLARE @Kr_accommodationType INT = NULL
DECLARE @Kr_accommodationTypeName NVARCHAR(50) = NULL

...