編集する

SQLServer の列名取得

sqlserver

以下のクエリでカラム名を取得できます。

SELECT *
FROM   sys.columns
WHERE  object_id = (
	SELECT object_id
	FROM   sys.tables
	WHERE  name = 'テーブル名'
)

出力例

object_id	name	column_id	system_type_id	user_type_id	max_length	precision	scale	collation_name	is_nullable	is_ansi_padded	is_rowguidcol	is_identity	is_computed	is_filestream	is_replicated	is_non_sql_subscribed	is_merge_published	is_dts_replicated	is_xml_document	xml_collection_id	default_object_id	rule_object_id	is_sparse	is_column_set
388196433	ticket_id	1	127	127	8	19	0	NULL	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0
388196433	ticket_status	4	48	48	1	3	0	NULL	1	0	0	0	0	0	0	0	0	0	0	0	404196490	0	0	0
388196433	ticket_price	7	56	56	4	10	0	NULL	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
388196433	ticket_create_time	10	61	61	8	23	3	NULL	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
388196433	ticket_update_time	11	61	61	8	23	3	NULL	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

SELECT 
ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'テーブル名'
ORDERBY ORDINAL_POSITION

出力例

1	ticket_id	bigint	t_ticket
4	ticket_status	tinyint	t_ticket
7	ticket_price	int	t_ticket
10	ticket_create_time	datetime	t_ticket
11	ticket_update_time	datetime	t_ticket

主キーも取得する場合の例

SELECT T1.ORDINAL_POSITION as no, T1.COLUMN_NAME as field, T1.DATA_TYPE as type, T1.TABLE_NAME as tbl, 
 CASE WHEN T2.COLUMN_NAME IS NULL THEN '' ELSE 'PRI' END as 'key'

FROM INFORMATION_SCHEMA.COLUMNS T1
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE T2 
	ON T1.TABLE_NAME=T2.TABLE_NAME AND T1.COLUMN_NAME=T2.COLUMN_NAME 
WHERE T1.TABLE_NAME = 'テーブル名'
ORDER BY T1.ORDINAL_POSITION

出力例

no	field	type	tbl	key
1	ticket_id	bigint	t_ticket	PRI
4	ticket_status	tinyint	t_ticket
7	ticket_price	int	t_ticket
10	ticket_create_time	datetime	t_ticket
11	ticket_update_time	datetime	t_ticket

AUTHOR

@えのえの

最終更新日 2021/02/20

FAVORITE

LINK TAG
記法を見る