作成
SQLServer の列名取得
以下のクエリでカラム名を取得できます。 ###source SELECT * FROM sys.columns WHERE object_id = ( SELECT object_id FROM sys.tables WHERE name = 'テーブル名' ) ###table ret (出力例) 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 ###source SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'テーブル名' ORDERBY ORDINAL_POSITION ###table ret (出力例) 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 ### 主キーも取得する場合の例 ###source 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 ###table ret (出力例) 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
wakatta 記法
投稿の仕方
基本的な記法
エンジニア向け記法
記法のサンプル
このコンテンツを見る