[DB] 테이블의 컬럼명만 골라서 추출하기

데이터베이스에서 INSERT나 SELECT 시 명시적으로 컬럼명을 지정하여야 하는 경우가 있습니다.

특히 INSERT문을 작성할 때는 컬럼명을 일부러 쓰는게 여간 불편한게 아닙니다.

그래서 테이블의 컬럼명만 골라서 추출하는 쿼리정보를 공유하고자 합니다.

 

 

< 컬럼명만 조회하기 >

 

목차(Contents)

 

컬럼명 추출 용도

테이블에 컬럼이 몇 십개씩 있는 경우가 바람직한 케이스는 아닙니다.

하지만 필요에 따라 컬럼이 엄청 많은 경우들도 있습니다. 

SELECT는 필요한 컬럼을 지정하는 것이므로 별 필요가 없지만,  INSERT문의 경우 컬럼명을 지정해야 하는 경우가 있습니다.

이럴 때 유용하게 사용할 수 있는 쿼리입니다.

 

 

예시

예시 데이터는 시스템DB 인 msdb의 backupfile 테이블로 해보겠습니다.

sp_help를 사용해서 backupfile의 정보를 보니 컬럼이 많네요.

 

< msdb 테이블 정보 >

 

일부만 캡쳐했습니다. 전체 컬럼수는 26개네요.

컬럼명도 어려운데 저 컬럼명을 일일이 입력하면서 쿼리를 치는 건 좀 일이 많아 보이네요.

그러면 컬럼명만 뽑아내 보도록 하겠습니다.

 

컬럼명 출력 쿼리(일반테이블)

보통 INSERT 문에 사용되는 컬럼명은 컬럼명1, 컬럼명2, 컬럼명3, 컬럼명4, 컬럼명5, ...

이런식으로 사용되니 중간에 구분자를 쉼표(,)로 하겠습니다.

SQL 명령어는 STRING_AGG를 사용하겠습니다. 전에 문자열 합치기에 관련하여 올린 글이 있어 해당 포스트를 아래에 첨부할께요.

 

2022.12.27 - [IT테크/DB] - [MS-SQL] 컬럼 합치는 방법 3가지(STUFF, STRING_AGG)

[MS-SQL] 컬럼 합치는 방법 3가지(STUFF, STRING_AGG)

 

--컬럼명 출력(일반테이블)
SELECT STRING_AGG(COLUMN_NAME, ',' ) WITHIN GROUP(ORDER BY ORDINAL_POSITION)  
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'backupfile'

 

위의 쿼리가 컬럼명을 이어서 출력해주는 쿼리입니다.

위의 쿼리를 실행하면 아래와 같이 나옵니다.

 

< 컬럼명만 뽑는 쿼리 결과 >

 

파란색으로 표시된 부분을 복사해서 필요하신 부분을 골라 사용하시면 됩니다.

별거 아닌 쿼리지만 은근히 사용할 일이 많아 공유합니다.

 

 

컬럼명 출력 쿼리(임시테이블)

추가로 임시테이블에서도 확인할 일이 생겨서 임시테이블용 쿼리도 공유합니다.

임시테이블에서 확인할 때는 약간 쿼리가 다릅니다.

 

SELECT STRING_AGG(NAME, ',' ) WITHIN GROUP(ORDER BY column_id)  
  FROM tempdb.sys.columns 
 WHERE OBJECT_ID = OBJECT_ID('msdb..#backupfile')

 

 

그리고 복사 안된다고 하셔서 쿼리를 텍스트로도 올립니다.

 

일반테이블용 쿼리

SELECT STRING_AGG(COLUMN_NAME, ',' ) WITHIN GROUP(ORDER BY ORDINAL_POSITION)  
    FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'backupfile'

 

임시테이블용 쿼리

SELECT STRING_AGG(NAME, ',' ) WITHIN GROUP(ORDER BY column_id)  
    FROM tempdb.sys.columns 
 WHERE OBJECT_ID = OBJECT_ID('msdb..#backupfile')

 

2022.12.12 - [IT테크/DB] - [MS-SQL]프로시저, 테이블명 검색

[MS-SQL]프로시저, 테이블명 검색