Pertanyaan sederhana, bagaimana cara daftar primary key dari suatu tabel dengan T-SQL? Aku tahu bagaimana untuk mendapatkan indeks di atas meja, tetapi dapat't ingat bagaimana untuk mendapatkan 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>'
It's umumnya direkomendasikan praktek sekarang untuk menggunakan sys.*
pemandangan INFORMATION_SCHEMA
di SQL Server, jadi kecuali anda're perencanaan migrasi database saya akan menggunakan mereka. Berikut ini's bagaimana anda akan melakukannya dengan sys.*
views:
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>');
Ini adalah solusi yang hanya menggunakan sys-tabel.
Ini daftar semua tombol utama di database. Kembali skema, nama table, nama kolom dan benar kolom sort order untuk setiap primary key.
Jika anda ingin mendapatkan kunci primer untuk tabel tertentu, maka anda perlu untuk filter pada SchemaName
dan Namatabel
.
IMHO, solusi ini sangat generik dan tidak menggunakan string literal, sehingga akan berjalan pada mesin apapun.
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 ;
Berikut ini's cara lain dari pertanyaan dapatkan tabel primary key menggunakan sql query:
SELECT COLUMN_NAME
DARI INFORMATION_SCHEMA.KEY_COLUMN_USAGE
DI MANA OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA+'.'+CONSTRAINT_NAME), 'IsPrimaryKey') = 1
DAN TABLE_NAME = '<meja anda nama>'
Menggunakan KEY_COLUMN_USAGE
untuk menentukan kendala untuk tabel tertentu
Kemudian menggunakan OBJECTPROPERTY(id, 'IsPrimaryKey')
untuk menentukan apakah masing-masing adalah kunci utama
Menggunakan MS SQL Server anda dapat melakukan hal-hal berikut:
--List all tables primary keys
select * from information_schema.table_constraints
where constraint_type = 'Primary Key'
Anda juga dapat menyaring pada table_name kolom jika anda ingin meja tertentu.
--Ini adalah Versi Modifikasi yang adalah juga sebuah contoh untuk Co-Terkait Query
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')
Ini harus daftar semua kendala ( primary Key dan Foreign key ) dan di akhir query masukan nama tabel
/* 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;
Untuk referensi silahkan baca thru - 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
Terima Kasih Orang.
Dengan sedikit variasi saya menggunakannya untuk menemukan semua kunci utama untuk semua tabel.
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
Berikut query akan menampilkan kunci primer dari tabel tertentu:
SELECT DISTINCT
CONSTRAINT_NAME AS [Constraint],
TABLE_SCHEMA AS [Schema],
TABLE_NAME AS TableName
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_NAME = 'mytablename'
Saya menceritakan sebuah Teknik sederhana yang saya ikuti
SP_HELP 'table_name'
menjalankan kode ini sebagai query. Menyebutkan nama tabel di tempat table_name yang anda ingin tahu Kunci Primer (don't lupa tanda kutip tunggal). Hasilnya akan tampil seperti Gambar terlampir. Berharap ini akan membantu anda
Jika Primary Key dan jenis yang dibutuhkan, query ini mungkin berguna:
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
Versi ini menampilkan skema, tabel nama dan memerintahkan, daftar dipisahkan koma dari kunci primer. Object_Id() tidak bekerja untuk link server jadi kita filter dengan nama tabel.
Tanpa MENGGANTI(Si1.Column_Name, '', '') itu akan menunjukkan xml tag pembuka dan penutup untuk Column_Name pada database saya adalah pengujian pada. Saya tidak yakin mengapa database diperlukan untuk menggantikan 'Column_Name' jadi jika seseorang tahu maka silakan komentar.
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;
Dan pola yang sama menggunakan George's query:
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.Objek Tabel berisi baris untuk setiap pengguna-didefinisikan, skema-scoped objek .
Kendala dibuat seperti Primary Key atau orang lain akan menjadi * objek dan nama Tabel akan menjadi parent_object*
Query sys.Benda-benda dan mengumpulkan Objek's Id Diperlukan Jenis
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)
Untuk daftar dipisahkan koma dari kolom kunci utama untuk diberikan TableName dan Skema:
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)
Mungkin saya sarankan yang lebih akurat jawaban sederhana untuk pertanyaan yang asli di bawah ini
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'
Catatan: