[MS-SQL] System Table을 활용하여 컬럼정보 조회하기

1. Procedure Create

/*************************************************************************************
' Procedure Name : sp_SelectTableColumnInfo_Second
' Written date : 2007.03.27
' Writer : Bo H. Choi
' Contents : Table Information Select ( Column Name, Data Type, Column Length )
' Updated by :
' Updated date :
' Updated contents :
' Sample : exec sp_SelectTableColumnInfo_Second 'Authors', 1
'                     exec sp_SelectTableColumnInfo_Second 'Authors', 2

'             exec sp_SelectTableColumnInfo_Second 'Authors', 3
*************************************************************************************/
CREATE  PROC sp_SelectTableColumnInfo_Second
(
 @TABLE  VARCHAR (128),  -- Table Name
 @TYPE  VARCHAR (10)
)
AS

DECLARE @sqlStr VARCHAR(8000)

-- INSERT 쿼리작성시 SELECT절 참고
IF @TYPE = 1
SET @sqlStr = '
  SELECT  C.name + '','' AS colNm, ''@''  + C.name + '','' AS colValue
  FROM syscolumns C, systypes T
  WHERE C.XUserType = T.XUserType AND C.id IN 
  (
   SELECT id
   FROM sysobjects 
   WHERE name =''' + @table + '''
  ) 
   '

 

-- 프로시저 작성시 참고
ELSE IF @TYPE = 2
 SET @sqlStr = '
  SELECT  ''@'' + UPPER(C.name),
   CASE UPPER(T.name)
    WHEN ''INT'' THEN UPPER(T.name) + '',''
   ELSE UPPER(T.name) + ''('' + CAST(C.length AS VARCHAR) + ''),''
   END
  FROM syscolumns C, systypes T
  WHERE C.XUserType = T.XUserType AND C.id IN 
  (
   SELECT id
   FROM sysobjects 
   WHERE name =''' + @table + '''
  ) 
  ORDER BY colorder
  '

-- 컬럼정보 조회하기
ELSE IF @TYPE = 3
 SET @sqlStr = '
  SELECT C.name ,T.name , C.length
  FROM syscolumns C, systypes T
  WHERE C.XUserType = T.XUserType AND C.id IN 
  (
      SELECT id
      FROM sysobjects 
      WHERE name =''' + @table + '''
  ) 
  ORDER BY colorder
  '

EXEC (@sqlStr)

2. 사용방법

- exec sp_SelectTableColumnInfo_Second 'Authors', 1

- exec sp_SelectTableColumnInfo_Second 'Authors', 2

 

3. 결과

- @TYPE = 1인 경우 Insert쿼리 생성시 Select절에 참고

- @TYPE = 2인 경우 프로시저 생성시 참고