簡単な質問ですが、T-SQLでテーブルの主キーをリストアップする方法を教えてください。テーブルのインデックスを取得する方法は知っていますが、PKを取得する方法を思い出せません。
SELECT Col.Column_Name from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE
Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY'
AND Col.Table_Name = '<your table name>'
sysを使用することは、一般的に推奨される方法です。*
SQL Serverの INFORMATION_SCHEMA
を表示するため、データベースの移行を計画していない限り、それらを使用します。 sysでそれを行う方法は次のとおりです。*
ビュー:
SELECT
c.name AS column_name,
i.name AS index_name,
c.is_identity
FROM sys.indexes i
inner join sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
inner join sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1
and i.object_ID = OBJECT_ID('<schema>.<tablename>');
これは sys -tablesのみを使用するソリューションです。
データベース内のすべての主キーが一覧表示されます。 スキーマ、テーブル名、列名、および各主キーの正しい列並べ替え順序を返します。
特定のテーブルの主キーを取得する場合は、 SchemaName
およびTableName
をフィルタリングする必要があります。
私見、このソリューションは非常に一般的で、文字列リテラルを使用しないため、どのマシンでも実行されます。
select
s.name as SchemaName,
t.name as TableName,
tc.name as ColumnName,
ic.key_ordinal as KeyOrderNr
from
sys.schemas s
inner join sys.tables t on s.schema_id=t.schema_id
inner join sys.indexes i on t.object_id=i.object_id
inner join sys.index_columns ic on i.object_id=ic.object_id
and i.index_id=ic.index_id
inner join sys.columns tc on ic.object_id=tc.object_id
and ic.column_id=tc.column_id
where i.is_primary_key=1
order by t.name, ic.key_ordinal ;
質問の別の方法[sqlクエリを使用してテーブルの主キーを取得] [主キーを取得]:
<。!-言語:lang-sql -->。
SELECT COLUMN_NAME。
INFORMATION_SCHEMA.KEY_COLUMN_USAGEから。
ここでOBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '。'+ CONSTRAINT_NAME)、' IsPrimaryKey ')= 1。
AND TABLE_NAME = '< i>& lt;テーブル名& gt;< / i>'。
< / code>< / pre>。
KEY_COLUMN_USAGE
を使用して、特定のテーブルの制約を決定します。
次に、 OBJECTPROPERTY(< i> id< / i>、 'IsPrimaryKey')< / code>
を使用して、それぞれが主キーであるかどうかを判断します。
-これは、共同関連クエリの例でもある別の変更バージョンです。
SELECT TC.TABLE_NAME as [Table_name], TC.CONSTRAINT_NAME as [Primary_Key]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.TABLE_NAME IN
(SELECT [NAME] AS [TABLE_NAME] FROM SYS.OBJECTS
WHERE TYPE = 'U')
これにはすべての制約(プライマリキーと外部キー)がリストされ、クエリの最後にテーブル名が表示されます。
/* CAST IS DONE , SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT*/
WITH ALL_KEYS_IN_TABLE (CONSTRAINT_NAME,CONSTRAINT_TYPE,PARENT_TABLE_NAME,PARENT_COL_NAME,PARENT_COL_NAME_DATA_TYPE,REFERENCE_TABLE_NAME,REFERENCE_COL_NAME)
AS
(
SELECT CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)) ,
CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)) ,
PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)) ,
PARENT_COL_NAME=CAST ( PKnUKEYCol.name AS VARCHAR(30)) ,
PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,
REFERENCE_TABLE_NAME='' ,
REFERENCE_COL_NAME=''
FROM sys.key_constraints as PKnUKEY
INNER JOIN sys.tables as PKnUTable
ON PKnUTable.object_id = PKnUKEY.parent_object_id
INNER JOIN sys.index_columns as PKnUColIdx
ON PKnUColIdx.object_id = PKnUTable.object_id
AND PKnUColIdx.index_id = PKnUKEY.unique_index_id
INNER JOIN sys.columns as PKnUKEYCol
ON PKnUKEYCol.object_id = PKnUTable.object_id
AND PKnUKEYCol.column_id = PKnUColIdx.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
ON oParentColDtl.TABLE_NAME=PKnUTable.name
AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name
UNION ALL
SELECT CONSTRAINT_NAME= CAST (oConstraint.name AS VARCHAR(30)) ,
CONSTRAINT_TYPE='FK',
PARENT_TABLE_NAME=CAST (oParent.name AS VARCHAR(30)) ,
PARENT_COL_NAME=CAST ( oParentCol.name AS VARCHAR(30)) ,
PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,
REFERENCE_TABLE_NAME=CAST ( oReference.name AS VARCHAR(30)) ,
REFERENCE_COL_NAME=CAST (oReferenceCol.name AS VARCHAR(30))
FROM sys.foreign_key_columns FKC
INNER JOIN sys.sysobjects oConstraint
ON FKC.constraint_object_id=oConstraint.id
INNER JOIN sys.sysobjects oParent
ON FKC.parent_object_id=oParent.id
INNER JOIN sys.all_columns oParentCol
ON FKC.parent_object_id=oParentCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.parent_column_id=oParentCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
INNER JOIN sys.sysobjects oReference
ON FKC.referenced_object_id=oReference.id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
ON oParentColDtl.TABLE_NAME=oParent.name
AND oParentColDtl.COLUMN_NAME=oParentCol.name
INNER JOIN sys.all_columns oReferenceCol
ON FKC.referenced_object_id=oReferenceCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.referenced_column_id=oReferenceCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
)
select * from ALL_KEYS_IN_TABLE
where
PARENT_TABLE_NAME in ('YOUR_TABLE_NAME')
or REFERENCE_TABLE_NAME in ('YOUR_TABLE_NAME')
ORDER BY PARENT_TABLE_NAME,CONSTRAINT_NAME;
参考までに、http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspxまでお読みください。
SELECT t.name AS 'table', i.name AS 'index', it.xtype,
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 1
AND k.id = t.id)
AS 'column1',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 2
AND k.id = t.id)
AS 'column2',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 3
AND k.id = t.id)
AS 'column3',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 4
AND k.id = t.id)
AS 'column4',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 5
AND k.id = t.id)
AS 'column5',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 6
AND k.id = t.id)
AS 'column6',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 7
AND k.id = t.id)
AS 'column7',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 8
AND k.id = t.id)
AS 'column8',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 9
AND k.id = t.id)
AS 'column9',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 10
AND k.id = t.id)
AS 'column10',
FROM sysobjects t
INNER JOIN sysindexes i ON i.id = t.id
INNER JOIN sysobjects it ON it.parent_obj = t.id AND it.name = i.name
WHERE it.xtype = 'PK'
ORDER BY t.name, i.name
ありがとう。
少し変化して、すべてのテーブルのすべての主キーを見つけるためにそれを使用しました。
SELECT A.Name,Col.Column_Name from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col ,
(select NAME from dbo.sysobjects where xtype='u') AS A
WHERE
Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY '
AND Col.Table_Name = A.Name
SELECT A.TABLE_NAME as [Table_name], A.CONSTRAINT_NAME as [Primary_Key]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
以下のクエリには、特定のテーブル:のプライマリキーがリストされます。
SELECT DISTINCT
CONSTRAINT_NAME AS [Constraint],
TABLE_SCHEMA AS [Schema],
TABLE_NAME AS TableName
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_NAME = 'mytablename'
主キーとタイプが必要な場合は、このクエリが役立つ場合があります。
SELECT L.TABLE_SCHEMA, L.TABLE_NAME, L.COLUMN_NAME, R.TypeName
FROM(
SELECT COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
)L
LEFT JOIN (
SELECT
OBJECT_NAME(c.OBJECT_ID) TableName ,c.name AS ColumnName ,t.name AS TypeName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
)R ON L.COLUMN_NAME = R.ColumnName AND L.TABLE_NAME = R.TableName
このバージョンには、スキーマ、テーブル名、および順序付けられたコンマ区切りの主キーのリストが表示されます。 Object_Id()はリンクサーバーでは機能しないため、テーブル名でフィルタリングします。
REPLACE(Si1.Column_Name、 ''、 '')がないと、テストしていたデータベースにColumn_Nameのxml開閉タグが表示されます。 データベースに「Column_Name」の置換が必要な理由がわからないので、誰かが知っている場合はコメントしてください。
DECLARE @TableName VARCHAR(100) = '';
WITH Sysinfo
AS (SELECT Kcu.Table_Name
, Kcu.Table_Schema AS Schema_Name
, Kcu.Column_Name
, Kcu.Ordinal_Position
FROM [LinkServer].Information_Schema.Key_Column_Usage Kcu
JOIN [LinkServer].Information_Schema.Table_Constraints AS Tc ON Tc.Constraint_Name = Kcu.Constraint_Name
WHERE Tc.Constraint_Type = 'Primary Key')
SELECT Schema_Name
,Table_Name
, STUFF(
(
SELECT ', '
, REPLACE(Si1.Column_Name, '', '')
FROM Sysinfo Si1
WHERE Si1.Table_Name = Si2.Table_Name
ORDER BY Si1.Table_Name
, Si1.Ordinal_Position
FOR XML PATH('')
), 1, 2, '') AS Primary_Keys
FROM Sysinfo Si2
WHERE Table_Name = CASE
WHEN @TableName NOT IN( '', 'All')
THEN @TableName
ELSE Table_Name
END
GROUP BY Si2.Table_Name, Si2.Schema_Name;
そして、ジョージのクエリを使用した同じパターン:
DECLARE @TableName VARCHAR(100) = '';
WITH Sysinfo
AS (SELECT S.Name AS Schema_Name
, T.Name AS Table_Name
, Tc.Name AS Column_Name
, Ic.Key_Ordinal AS Ordinal_Position
FROM [LinkServer].Sys.Schemas S
JOIN [LinkServer].Sys.Tables T ON S.Schema_Id = T.Schema_Id
JOIN [LinkServer].Sys.Indexes I ON T.Object_Id = I.Object_Id
JOIN [LinkServer].Sys.Index_Columns Ic ON I.Object_Id = Ic.Object_Id
AND I.Index_Id = Ic.Index_Id
JOIN [LinkServer].Sys.Columns Tc ON Ic.Object_Id = Tc.Object_Id
AND Ic.Column_Id = Tc.Column_Id
WHERE I.Is_Primary_Key = 1)
SELECT Schema_Name
,Table_Name
, STUFF(
(
SELECT ', '
, REPLACE(Si1.Column_Name, '', '')
FROM Sysinfo Si1
WHERE Si1.Table_Name = Si2.Table_Name
ORDER BY Si1.Table_Name
, Si1.Ordinal_Position
FOR XML PATH('')
), 1, 2, '') AS Primary_Keys
FROM Sysinfo Si2
WHERE Table_Name = CASE
WHEN @TableName NOT IN('', 'All')
THEN @TableName
ELSE Table_Name
END
GROUP BY Si2.Table_Name, Si2.Schema_Name;
Sys.Objectsテーブルには、ユーザー定義のスキーマスコープされた各行が含まれています。 オブジェクト。 . 。 プライマリキーなどのように作成された制約はオブジェクトになります。 テーブル名は parent_object になります。 。 sys.Objectをクエリし、オブジェクトの必要タイプのIDを収集します。
declare @TableName nvarchar(50)='TblInvoice' -- your table name
declare @TypeOfKey nvarchar(50)='PK' -- For Primary key
SELECT Name FROM sys.objects
WHERE type = @TypeOfKey
AND parent_object_id = OBJECT_ID (@TableName)
特定のTableNameとスキーマの主要なキー列のコンマ区切りのリストの場合:
Select distinct SUBSTRING ( stuff(( select distinct ',' + [COLUMN_NAME]
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND TABLE_NAME = 'TableName' AND TABLE_SCHEMA = 'Schema'
order by 1 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,0,'' )
,2,9999)
以下の元の質問に対するより正確な簡単な回答を提案できますか。
SELECT
KEYS.table_schema, KEYS.table_name, KEYS.column_name, KEYS.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE keys
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS
ON cons.TABLE_SCHEMA = keys.TABLE_SCHEMA
AND cons.TABLE_NAME = keys.TABLE_NAME
AND cons.CONSTRAINT_NAME = keys.CONSTRAINT_NAME
WHERE cons.CONSTRAINT_TYPE = 'PRIMARY KEY'
注意:
1。 上記の回答の一部には、主キーだけのフィルターがありません。 列。! 2。 以下をCTEで使用して、より大きな列に参加します。 ソースからメタデータを提供して、BIML生成のステージングテーブルとSSISコードをフィードするためのリスト。