俺氏、本を読む

30歳になるまでに本を読んで勉強しようかと。主に啓発、お金についての本を読むつもり。一応プログラマーなのでその辺のことも。あと、せどり（転売）の仕入れ見込み商品をリサーチして仕入先と一緒に投稿します

【SQLServer】テーブルの主キー以外の列名を取得

SQLServer

テーブルの主キー以外の列名を取得する方法。

SQLServer 2008でしか確認していないですけど、

とりあえず情報スキーマビュー、システムテーブル、システムビューから取得する方法。

各バージョンでのマッピングは以下参照

システム テーブルのシステム ビューへのマッピング (Transact-SQL)

 

　

情報スキーマビュー

SELECT COLS.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS COLS
WHERE COLS.TABLE_NAME = N'テーブル名'
  AND NOT EXISTS(
    			SELECT 1 
				FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS KEYS
				WHERE KEYS.TABLE_NAME = COLS.TABLE_NAME
				  AND KEYS.COLUMN_NAME = COLS.COLUMN_NAME
  )
ORDER BY COLS.ORDINAL_POSITION

　

システムテーブル

SELECT COLS.name
FROM syscolumns AS COLS
INNER JOIN sysobjects AS OBJ
 ON (OBJ.id=COLS.id)
WHERE OBJ.name=N'テーブル名'
  AND NOT EXISTS(
    			SELECT 1 
				FROM sysindexes AS IDX
				INNER JOIN sysindexkeys AS KEYS
				 ON KEYS.indid=IDX.indid
				WHERE KEYS.id=OBJ.id
				  AND KEYS.colid=COLS.colid
  )
ORDER BY COLS.colid

　

システムビュー

SELECT COLS.name
FROM sys.columns AS COLS
INNER JOIN sys.objects AS OBJ
 ON (OBJ.object_id=COLS.object_id)
WHERE OBJ.name=N'テーブル名'
  AND NOT EXISTS(
    			SELECT 1 
				FROM sys.indexes AS IDX
				INNER JOIN sys.index_columns  AS KEYS
				 ON KEYS.index_id=IDX.index_id
				AND KEYS.object_id=COLS.object_id
				WHERE KEYS.object_id=OBJ.object_id
				  AND KEYS.column_id=COLS.column_id
  )
ORDER BY COLS.column_id

　

逆に主キーの項目取得したい場合は、

「NOT EXISTS」を「EXISTS」にするか、

キー情報が入ってるテーブルをJOINするか

順番とか気にしないのであれば以下のような感じでも

 

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = N'テーブル名'