테이블 필드들 콤마로 나열하는 함수

테이블 필드들 콤마로 나열하는 함수

 

[일반]

 

-- 사용형식 : SELECT [db명].[스키마명].[fn_GetTableColWithComma_ForCHS] '테이블명'

CREATE FUNCTION [dbo].[fn_GetTableColWithComma_ForCHS]( @iTable VARCHAR(100) -- 테이블명 ) RETURNS VARCHAR(1000) AS BEGIN DECLARE @wCols varchar(1000) SELECT @wCols = COALESCE(@wCols + ',' + Name, Name) FROM sys.syscolumns WHERE ID = Object_Id(@iTable) ORDER BY ColId RETURN @wCols END

 

결과 : A필드,B필드,C필드,D필드...

 

 

[ISNULL 함수 포함]

 

DataType 에 따라 ISNULL 구문이 틀려짐

 

-- 사용형식 : SELECT [db명].[스키마명].[fn_GetTableColWithComma_ForCHS2] '테이블명'

CREATE FUNCTION [dbo].[fn_GetTableColWithComma_ForCHS2]( @iTable VARCHAR(100) -- 테이블명 ) RETURNS VARCHAR(8000) AS BEGIN DECLARE @wCols varchar(1000) SELECT @wCols = COALESCE(@wCols + ',' + CASE WHEN LOWER(Data_Type) IN ('numeric','smallint','int','money','tinyint','float','bigint') THEN 'ISNULL(' + Column_Name + ',0) ' + Column_Name WHEN LOWER(Data_Type) IN ('datetime','smalldatetime') THEN 'ISNULL(' + Column_Name + ',''''1900-01-01'''') ' + Column_Name ELSE 'ISNULL(' + Column_Name + ','''''''') ' + Column_Name END, CASE WHEN LOWER(Data_Type) IN ('numeric','smallint','int','money','tinyint','float','bigint') THEN 'ISNULL(' + Column_Name + ',0) ' + Column_Name WHEN LOWER(Data_Type) IN ('datetime','smalldatetime') THEN 'ISNULL(' + Column_Name + ',''''1900-01-01'''') ' + Column_Name ELSE 'ISNULL(' + Column_Name + ','''''''') ' + Column_Name END) FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = @iTable ORDER BY Ordinal_Position RETURN @wCols END

 

결과 : ISNULL(A필드,'') A필드,ISNULL(B필드,0) B필드,ISNULL(C필드,'1900-01-01') C필드,...