【SQLServer】テーブルのカラム名一覧を取得

INSERT文を書くときなど、カラム名(列名)の一覧が欲しい時が結構あります。
そういったときに便利なテクニックです。カンマ区切りで横並びで表示できます。
サンプルとしてテストテーブルに対して実行します。

テストテーブル作成

SQL

--テストテーブル作成
create table test
(
	[id] int,
	[Name] varchar(max),
	[number1] int,
	[number2] int,
	[number3] int,
	[number4] int,
	[number5] int,
	[number6] int,
	[number7] int,
	[number8] int,
	[number9] int,
)

コード例

変数にテーブル名、検索文字列を設定して実行します。

①全ての列を取得

SQL

--出力対象
DECLARE @cols varchar(max)					
--検索対象テーブル
DECLARE @table_name varchar(max) = 'test'	
--検索文字列("%"=任意の文字)
DECLARE @Str varchar(max) = '%'		

--列名取得
set @cols = (SELECT column_name+ ',' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name and column_name LIKE @Str FOR XML PATH(''))

--結果表示
print @cols

結果
Name,id,number1,number2,number3,number4,number5,number6,number7,number8,number9,

②”number”から始まるカラム名を取得

SQL

--出力対象
DECLARE @cols varchar(max)					
--検索対象テーブル
DECLARE @table_name varchar(max) = 'test'	
--検索文字列("_"=任意の１文字)
DECLARE @Str varchar(max) = 'number_'		

--列名取得
set @cols = (SELECT column_name+ ','  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name and column_name LIKE @Str FOR XML PATH(''))

--結果表示
print @cols

結果
number1,number2,number3,number4,number5,number6,number7,number8,number9,