blocks|key|1540653|text|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>'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1540654|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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 = '&lt;your table name&gt;'
</code></pre>

blocks|key|1755944|text|现在通常推荐的做法是在SQL+Server中通过INFORMATION_SCHEMA使用sys.*视图，因此除非您计划迁移数据库，否则我将使用这些视图。下面是如何使用sys.*视图完成此操作：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1755945|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>');|code-block|syntax|javascript|1755946|entityMap^0|O|I|18|5|2B|5|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

It's generally recommended practice now to use the <code>sys.*</code> views over <code>INFORMATION_SCHEMA</code> in SQL Server, so unless you're planning on migrating databases I would use those. Here's how you would do it with the <code>sys.*</code> views:

<pre><code>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('&lt;schema&gt;.&lt;tablename&gt;');
</code></pre>

blocks|key|1540674|text|使用MS+SQL+Server时，您可以执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1540675|--List+all+tables+primary+keys
select+*+from+information_schema.table_constraints
where+constraint_type+=+'Primary+Key'|code-block|syntax|javascript|1540676|如果需要特定表，还可以根据table_name列进行筛选。|1540677|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Is using MS SQL Server you can do the following: 

<pre><code>--List all tables primary keys
select * from information_schema.table_constraints
where constraint_type = 'Primary Key'
</code></pre>

You can also filter on the table_name column if you want a specific table.

blocks|key|1755625|text|我喜欢INFORMATION_SCHEMA技术，但我使用的另一个方法是:+exec+sp_pkeys+'table‘|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1755626|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I like the INFORMATION_SCHEMA technique, but another I've used is:
exec sp_pkeys 'table'

blocks|key|1540834|text|--这是另一个修改版本，也是相关查询的示例|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1540835|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')|code-block|syntax|javascript|1540836|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

--This is another Modified Version which is also an example for Co-Related Query

<pre><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')
</code></pre>

blocks|key|1541148|text|我正在讲一个我遵循的简单技巧。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1541149|SP_HELP+'table_name'|code-block|syntax|javascript|1541150|将此代码作为查询运行。在你想知道主键的table_name处提到你的表名(别忘了单引号)。结果将显示like+attached+Image。希望能对你有所帮助|1541151|​|1541152|📷|atomic|offset|length|1541153|1541154|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/398e42f2426d173b5a0c430b416fd7fe.png|imageAlt^0|0|0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|11|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|12|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|13|8|@]|9|@$N|14|O|15|1|16]]|A|$]]|$1|P|3|J|5|6|7|17|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X|Y|-4]]]]

I am telling a simple Technic which I follow 

<pre><code>SP_HELP 'table_name'
</code></pre>

run this code as query. Mention your table name at place of table_name for which you want to know Primary Key (don't forget the single quotes). The result will show like attached Image. Hope it will help you

<a href="https://i.stack.imgur.com/fGpNu.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fGpNu.png" alt="enter image description here"></a>

blocks|key|1540637|text|系统存储过程sp_help将为您提供信息。执行以下语句：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1540638|execute+sp_help+table_name|code-block|syntax|javascript|1540639|entityMap^0|6|7|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

The system stored procedure <code>sp_help</code> will give you the information. Execute the following statement:

<pre><code>execute sp_help table_name
</code></pre>

blocks|key|1540696|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1540697|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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
</code></pre>

blocks|key|1755863|text|这一条给出了主键的列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1755864|SELECT+COLUMN_NAME+FROM+INFORMATION_SCHEMA.KEY_COLUMN_USAGE+WHERE+TABLE_NAME+=+'TableName'|code-block|syntax|javascript|1755865|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This one gives you the columns that are PK.

<pre><code>SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'TableName'
</code></pre>

blocks|key|1755685|text|谢谢，盖伊。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1755686|稍有不同，我使用它来查找所有表的所有主键。|1755687|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|code-block|syntax|javascript|1755688|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Thanks Guy.

With a slight variation I used it to find all the primary keys for all the tables.

<pre><code>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
</code></pre>

blocks|key|1540807|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1540808|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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
</code></pre>

blocks|key|1756207|text|如果您正在寻找自己的ORM或从给定表生成代码，那么这可能就是您正在寻找的形式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1756208|declare+@table+varchar(100)+=+'mytable';

with+cte+as
(
++++select+
++++++++tc.CONSTRAINT_SCHEMA
++++++++,+tc.CONSTRAINT_TYPE
++++++++,+tc.TABLE_NAME
++++++++,+ccu.COLUMN_NAME
++++++++,+IS_NULLABLE
++++++++,+DATA_TYPE
++++++++,+CHARACTER_MAXIMUM_LENGTH
++++++++,+NUMERIC_PRECISION
++++from+
++++++++INFORMATION_SCHEMA.TABLE_CONSTRAINTS+tc+
++++++++inner+join+INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE+ccu+on+tc.TABLE_NAME=ccu.TABLE_NAME++and+tc.TABLE_SCHEMA=ccu.TABLE_SCHEMA
++++++++inner+join+information_schema.COLUMNS+c+on+ccu.COLUMN_NAME=c.COLUMN_NAME+and+ccu.TABLE_NAME=c.TABLE_NAME+and+ccu.TABLE_SCHEMA=c.TABLE_SCHEMA
++++where+
++++++++tc.table_name=@table
++++++++and+
++++++++ccu.CONSTRAINT_NAME=tc.CONSTRAINT_NAME
++++union+
++++select+TABLE_SCHEMA,'COLUMN',+TABLE_NAME,+COLUMN_NAME,+IS_NULLABLE,+DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,+NUMERIC_PRECISION+from+INFORMATION_SCHEMA.COLUMNS+where+TABLE_NAME=@table
++++and+COLUMN_NAME+not+in+(select+COLUMN_NAME+from+INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE+where+TABLE_NAME+=+@table)
)
select+
++++cast(iif(CONSTRAINT_TYPE='PRIMARY+KEY',1,0)+as+bit)+PrimaryKey
++++,cast(iif(CONSTRAINT_TYPE='FOREIGN+KEY',1,0)+as+bit)+ForeignKey
++++,cast(iif(CONSTRAINT_TYPE='COLUMN',1,0)+as+bit)+NotKey
++++,COLUMN_NAME
++++,cast(iif(is_nullable='NO',0,1)+as+bit)+IsNullable
++++,+DATA_TYPE
++++,+CHARACTER_MAXIMUM_LENGTH
++++,+NUMERIC_PRECISION+
from+
++++cte+
order+by+
++++case+CONSTRAINT_TYPE+
++++++++when+'PRIMARY+KEY'+then+1+
++++++++when+'FOREIGN+KEY'+then+2+
++++++++else+3+end
++++,+COLUMN_NAME|code-block|syntax|javascript|1756209|结果如下所示：|1756210|​|1756211|				<table+cellspacing=0+border=1>
					<tr>
						<td+style=min-width:50px>PrimaryKey</td>
						<td+style=min-width:50px>ForeignKey</td>
						<td+style=min-width:50px>NotKey</td>
						<td+style=min-width:50px>COLUMN_NAME</td>
						<td+style=min-width:50px>IsNullable</td>
						<td+style=min-width:50px>DATA_TYPE</td>
						<td+style=min-width:50px>CHARACTER_MAXIMUM_LENGTH</td>
						<td+style=min-width:50px>NUMERIC_PRECISION</td>
					</tr>
					<tr>
						<td+style=min-width:50px>1</td>
						<td+style=min-width:50px>0</td>
						<td+style=min-width:50px>0</td>
						<td+style=min-width:50px>LectureNoteID</td>
						<td+style=min-width:50px>0</td>
						<td+style=min-width:50px>int</td>
						<td+style=min-width:50px>NULL</td>
						<td+style=min-width:50px>10</td>
					</tr>
					<tr>
						<td+style=min-width:50px>0</td>
						<td+style=min-width:50px>1</td>
						<td+style=min-width:50px>0</td>
						<td+style=min-width:50px>LectureId</td>
						<td+style=min-width:50px>0</td>
						<td+style=min-width:50px>int</td>
						<td+style=min-width:50px>NULL</td>
						<td+style=min-width:50px>10</td>
					</tr>
					<tr>
						<td+style=min-width:50px>0</td>
						<td+style=min-width:50px>1</td>
						<td+style=min-width:50px>0</td>
						<td+style=min-width:50px>NoteTypeID</td>
						<td+style=min-width:50px>0</td>
						<td+style=min-width:50px>int</td>
						<td+style=min-width:50px>NULL</td>
						<td+style=min-width:50px>10</td>
					</tr>
					<tr>
						<td+style=min-width:50px>0</td>
						<td+style=min-width:50px>0</td>
						<td+style=min-width:50px>1</td>
						<td+style=min-width:50px>Body</td>
						<td+style=min-width:50px>0</td>
						<td+style=min-width:50px>nvarchar</td>
						<td+style=min-width:50px>-1</td>
						<td+style=min-width:50px>NULL</td>
					</tr>
					<tr>
						<td+style=min-width:50px>0</td>
						<td+style=min-width:50px>0</td>
						<td+style=min-width:50px>1</td>
						<td+style=min-width:50px>DisplayOrder</td>
						<td+style=min-width:50px>0</td>
						<td+style=min-width:50px>int</td>
						<td+style=min-width:50px>NULL</td>
						<td+style=min-width:50px>10</td>
					</tr>
				</table>|1756212|1756213|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Q|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|R|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|S|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|M|3|J|5|6|7|U|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|O|$]]

If you are looking to do your own ORM or generate code from a given table, then this might be what you are looking form:

<pre><code>declare @table varchar(100) = 'mytable';

with cte as
(
 select 
 tc.CONSTRAINT_SCHEMA
 , tc.CONSTRAINT_TYPE
 , tc.TABLE_NAME
 , ccu.COLUMN_NAME
 , IS_NULLABLE
 , DATA_TYPE
 , CHARACTER_MAXIMUM_LENGTH
 , NUMERIC_PRECISION
 from 
 INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
 inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu on tc.TABLE_NAME=ccu.TABLE_NAME and tc.TABLE_SCHEMA=ccu.TABLE_SCHEMA
 inner join information_schema.COLUMNS c on ccu.COLUMN_NAME=c.COLUMN_NAME and ccu.TABLE_NAME=c.TABLE_NAME and ccu.TABLE_SCHEMA=c.TABLE_SCHEMA
 where 
 tc.table_name=@table
 and 
 ccu.CONSTRAINT_NAME=tc.CONSTRAINT_NAME
 union 
 select TABLE_SCHEMA,'COLUMN', TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@table
 and COLUMN_NAME not in (select COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = @table)
)
select 
 cast(iif(CONSTRAINT_TYPE='PRIMARY KEY',1,0) as bit) PrimaryKey
 ,cast(iif(CONSTRAINT_TYPE='FOREIGN KEY',1,0) as bit) ForeignKey
 ,cast(iif(CONSTRAINT_TYPE='COLUMN',1,0) as bit) NotKey
 ,COLUMN_NAME
 ,cast(iif(is_nullable='NO',0,1) as bit) IsNullable
 , DATA_TYPE
 , CHARACTER_MAXIMUM_LENGTH
 , NUMERIC_PRECISION 
from 
 cte 
order by 
 case CONSTRAINT_TYPE 
 when 'PRIMARY KEY' then 1 
 when 'FOREIGN KEY' then 2 
 else 3 end
 , COLUMN_NAME
</code></pre>

Here is what the result would look like:

<div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
<div class="snippet-code">
<pre class="snippet-code-html lang-html prettyprint-override"><code>				&lt;table cellspacing=0 border=1&gt;
					&lt;tr&gt;
						&lt;td style=min-width:50px&gt;PrimaryKey&lt;/td&gt;
						&lt;td style=min-width:50px&gt;ForeignKey&lt;/td&gt;
						&lt;td style=min-width:50px&gt;NotKey&lt;/td&gt;
						&lt;td style=min-width:50px&gt;COLUMN_NAME&lt;/td&gt;
						&lt;td style=min-width:50px&gt;IsNullable&lt;/td&gt;
						&lt;td style=min-width:50px&gt;DATA_TYPE&lt;/td&gt;
						&lt;td style=min-width:50px&gt;CHARACTER_MAXIMUM_LENGTH&lt;/td&gt;
						&lt;td style=min-width:50px&gt;NUMERIC_PRECISION&lt;/td&gt;
					&lt;/tr&gt;
					&lt;tr&gt;
						&lt;td style=min-width:50px&gt;1&lt;/td&gt;
						&lt;td style=min-width:50px&gt;0&lt;/td&gt;
						&lt;td style=min-width:50px&gt;0&lt;/td&gt;
						&lt;td style=min-width:50px&gt;LectureNoteID&lt;/td&gt;
						&lt;td style=min-width:50px&gt;0&lt;/td&gt;
						&lt;td style=min-width:50px&gt;int&lt;/td&gt;
						&lt;td style=min-width:50px&gt;NULL&lt;/td&gt;
						&lt;td style=min-width:50px&gt;10&lt;/td&gt;
					&lt;/tr&gt;
					&lt;tr&gt;
						&lt;td style=min-width:50px&gt;0&lt;/td&gt;
						&lt;td style=min-width:50px&gt;1&lt;/td&gt;
						&lt;td style=min-width:50px&gt;0&lt;/td&gt;
						&lt;td style=min-width:50px&gt;LectureId&lt;/td&gt;
						&lt;td style=min-width:50px&gt;0&lt;/td&gt;
						&lt;td style=min-width:50px&gt;int&lt;/td&gt;
						&lt;td style=min-width:50px&gt;NULL&lt;/td&gt;
						&lt;td style=min-width:50px&gt;10&lt;/td&gt;
					&lt;/tr&gt;
					&lt;tr&gt;
						&lt;td style=min-width:50px&gt;0&lt;/td&gt;
						&lt;td style=min-width:50px&gt;1&lt;/td&gt;
						&lt;td style=min-width:50px&gt;0&lt;/td&gt;
						&lt;td style=min-width:50px&gt;NoteTypeID&lt;/td&gt;
						&lt;td style=min-width:50px&gt;0&lt;/td&gt;
						&lt;td style=min-width:50px&gt;int&lt;/td&gt;
						&lt;td style=min-width:50px&gt;NULL&lt;/td&gt;
						&lt;td style=min-width:50px&gt;10&lt;/td&gt;
					&lt;/tr&gt;
					&lt;tr&gt;
						&lt;td style=min-width:50px&gt;0&lt;/td&gt;
						&lt;td style=min-width:50px&gt;0&lt;/td&gt;
						&lt;td style=min-width:50px&gt;1&lt;/td&gt;
						&lt;td style=min-width:50px&gt;Body&lt;/td&gt;
						&lt;td style=min-width:50px&gt;0&lt;/td&gt;
						&lt;td style=min-width:50px&gt;nvarchar&lt;/td&gt;
						&lt;td style=min-width:50px&gt;-1&lt;/td&gt;
						&lt;td style=min-width:50px&gt;NULL&lt;/td&gt;
					&lt;/tr&gt;
					&lt;tr&gt;
						&lt;td style=min-width:50px&gt;0&lt;/td&gt;
						&lt;td style=min-width:50px&gt;0&lt;/td&gt;
						&lt;td style=min-width:50px&gt;1&lt;/td&gt;
						&lt;td style=min-width:50px&gt;DisplayOrder&lt;/td&gt;
						&lt;td style=min-width:50px&gt;0&lt;/td&gt;
						&lt;td style=min-width:50px&gt;int&lt;/td&gt;
						&lt;td style=min-width:50px&gt;NULL&lt;/td&gt;
						&lt;td style=min-width:50px&gt;10&lt;/td&gt;
					&lt;/tr&gt;
				&lt;/table&gt;
				</code></pre>
</div>
</div>

blocks|key|1541336|text|对于给定TableName和架构的以逗号分隔的主键列列表：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1541337|Select+distinct+SUBSTRING+(+stuff((+select+distinct+','+%2B+[COLUMN_NAME]+
++++++++++++++++++++++++++++++++++++from+INFORMATION_SCHEMA.KEY_COLUMN_USAGE++
++++++++++++++++++++++++++++++++++++where+OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA+%2B+'.'+%2B+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)+|code-block|syntax|javascript|1541338|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For a comma separated list of primary key columns for a given TableName and Schema:

<pre><code>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) 
</code></pre>

blocks|key|1755572|text|试一试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1755573|SELECT
++++CONSTRAINT_CATALOG+AS+DataBaseName,
++++CONSTRAINT_SCHEMA+AS+SchemaName,
++++TABLE_NAME+AS+TableName,
++++CONSTRAINT_Name+AS+PrimaryKey
FROM+INFORMATION_SCHEMA.TABLE_CONSTRAINTS+
WHERE+CONSTRAINT_TYPE+=+'Primary+Key'+and+Table_Name+=+'YourTable'|code-block|syntax|javascript|1755574|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Give this a try:

<pre><code>SELECT
 CONSTRAINT_CATALOG AS DataBaseName,
 CONSTRAINT_SCHEMA AS SchemaName,
 TABLE_NAME AS TableName,
 CONSTRAINT_Name AS PrimaryKey
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_TYPE = 'Primary Key' and Table_Name = 'YourTable'
</code></pre>

blocks|key|1540930|text|我发现这很有用，给出了一个表的列表，其中有一个逗号分隔的列列表，然后还有一个逗号分隔的列表，其中哪些是主键|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1540931|SELECT+T.TABLE_SCHEMA,+T.TABLE_NAME,+
STUFF((
++++SELECT+',+'+%2B+C.COLUMN_NAME
++++FROM+INFORMATION_SCHEMA.COLUMNS+C
++++++++WHERE+C.TABLE_SCHEMA+=+T.TABLE_SCHEMA
++++++++AND+T.TABLE_NAME+=+C.TABLE_NAME
++++++++FOR+XML+PATH+('')
++++),+1,+2,+'')+AS+Columns,
STUFF((
SELECT+',+'+%2B+C.COLUMN_NAME+
FROM+INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE+C
INNER+JOIN+INFORMATION_SCHEMA.TABLE_CONSTRAINTS+TC
++++ON+C.TABLE_SCHEMA+=+TC.TABLE_SCHEMA
++++AND+C.TABLE_NAME+=+TC.TABLE_NAME
++++WHERE+C.TABLE_SCHEMA+=+T.TABLE_SCHEMA
++++AND+T.TABLE_NAME+=+C.TABLE_NAME
++++AND+TC.CONSTRAINT_TYPE+=+'PRIMARY+KEY'
++++FOR+XML+PATH+('')
),+1,+2,+'')+AS+[Key]
FROM+INFORMATION_SCHEMA.TABLES+T
ORDER+BY+T.TABLE_SCHEMA,+T.TABLE_NAME|code-block|syntax|javascript|1540932|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I found this useful, gives a list of tables with a comma separate list of the columns and then also a comma separate list of which ones are the primary key

<pre><code>SELECT T.TABLE_SCHEMA, T.TABLE_NAME, 
STUFF((
 SELECT ', ' + C.COLUMN_NAME
 FROM INFORMATION_SCHEMA.COLUMNS C
 WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA
 AND T.TABLE_NAME = C.TABLE_NAME
 FOR XML PATH ('')
 ), 1, 2, '') AS Columns,
STUFF((
SELECT ', ' + C.COLUMN_NAME 
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
 ON C.TABLE_SCHEMA = TC.TABLE_SCHEMA
 AND C.TABLE_NAME = TC.TABLE_NAME
 WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA
 AND T.TABLE_NAME = C.TABLE_NAME
 AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
 FOR XML PATH ('')
), 1, 2, '') AS [Key]
FROM INFORMATION_SCHEMA.TABLES T
ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME
</code></pre>

blocks|key|1756009|text|此版本显示模式、表名和一个有序的、以逗号分隔的主键列表。Object_Id()不适用于链接服务器，因此我们按表名进行过滤。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1756010|如果不使用REPLACE(Si1.Column_Name，''，'')，它将显示我正在测试的数据库上Column_Name的开始标记和结束标记。我不确定为什么数据库需要'Column_Name‘的替换，所以如果有人知道，请评论。|1756011|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;|code-block|syntax|javascript|1756012|和使用George的查询的相同模式：|1756013|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;|1756014|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

This version displays the schema, the table name and an ordered, comma separated list of primary keys. Object_Id() does not work for link servers so we filter by the table name. 

Without the REPLACE(Si1.Column_Name, '', '') it would show the xml opening and closing tags for Column_Name on the database I was testing on. I am not sure why the database required a replace for 'Column_Name' so if someone knows then please comment.

<pre><code>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;
</code></pre>

And the same pattern using George's query:

<pre><code>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;
</code></pre>

blocks|key|1756125|text|可能是最近发布的，但希望这能帮助人们通过使用t-sql查询来查看sql+server中的主键列表：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1756126|SELECT++schema_name(t.schema_id)+AS+[schema_name],+t.name+AS+TableName,++++++++
++++COL_NAME(ic.OBJECT_ID,ic.column_id)+AS+PrimaryKeyColumnName,
++++i.name+AS+PrimaryKeyConstraintName
FROM++++sys.tables+t+
INNER+JOIN+sys.indexes+AS+i++on+t.object_id=i.object_id+
INNER+JOIN++sys.index_columns+AS+ic+ON++i.OBJECT_ID+=+ic.OBJECT_ID
++++++++++++++++++++++++++++AND+i.index_id+=+ic.index_id+
WHERE+OBJECT_NAME(ic.OBJECT_ID)+=+'YourTableNameHere'|code-block|syntax|javascript|1756127|如果需要，可以使用以下查询查看所有外键的列表：|1756128|SELECT
f.name+as+ForeignKeyConstraintName
,OBJECT_NAME(f.parent_object_id)+AS+ReferencingTableName
,COL_NAME(fc.parent_object_id,+fc.parent_column_id)+AS+ReferencingColumnName
,OBJECT_NAME+(f.referenced_object_id)+AS+ReferencedTableName
,COL_NAME(fc.referenced_object_id,+fc.referenced_column_id)+AS+
+ReferencedColumnName++,delete_referential_action_desc+AS+
DeleteReferentialActionDesc+,update_referential_action_desc+AS+
UpdateReferentialActionDesc
FROM+sys.foreign_keys+AS+f
INNER+JOIN+sys.foreign_key_columns+AS+fc
ON+f.object_id+=+fc.constraint_object_id
+--WHERE+OBJECT_NAME(f.parent_object_id)+=+'YourTableNameHere'+
+--If+you+want+to+know+referecing+table+details+
+WHERE+OBJECT_NAME(f.referenced_object_id)+=+'YourTableNameHere'+
+--If+you+want+to+know+refereced+table+details+
ORDER+BY+f.name|1756129|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Might be lately posted but hopefully this will help someone to see primary key list in sql server by using this t-sql query:

<pre><code>SELECT schema_name(t.schema_id) AS [schema_name], t.name AS TableName, 
 COL_NAME(ic.OBJECT_ID,ic.column_id) AS PrimaryKeyColumnName,
 i.name AS PrimaryKeyConstraintName
FROM sys.tables t 
INNER JOIN sys.indexes AS i on t.object_id=i.object_id 
INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID
 AND i.index_id = ic.index_id 
WHERE OBJECT_NAME(ic.OBJECT_ID) = 'YourTableNameHere'
</code></pre>

You can see the list of all foreign keys by using this query if you may want:

<pre><code>SELECT
f.name as ForeignKeyConstraintName
,OBJECT_NAME(f.parent_object_id) AS ReferencingTableName
,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ReferencingColumnName
,OBJECT_NAME (f.referenced_object_id) AS ReferencedTableName
,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS 
 ReferencedColumnName ,delete_referential_action_desc AS 
DeleteReferentialActionDesc ,update_referential_action_desc AS 
UpdateReferentialActionDesc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
 --WHERE OBJECT_NAME(f.parent_object_id) = 'YourTableNameHere' 
 --If you want to know referecing table details 
 WHERE OBJECT_NAME(f.referenced_object_id) = 'YourTableNameHere' 
 --If you want to know refereced table details 
ORDER BY f.name
</code></pre>

blocks|key|1756242|text|如果需要主键和类型，则此查询可能很有用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1756243|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+%2B+'.'+%2B+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|code-block|syntax|javascript|1756244|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If Primary Key and type needed, this query may be useful:

<pre><code>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
</code></pre>

blocks|key|1541364|text|可能是最简单的解决方案:)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1541365|EXEC+sp_pkeys+YourTable|offset|length|style|CODE|1541366|entityMap^0|0|0|N|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Probably the simplest solution :)
<code>EXEC sp_pkeys YourTable</code>

blocks|key|1541403|text|如果您在Oracle中需要它，那么它非常简单。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1541404|SELECT+`Constraint_Name`
++FROM+`All_Constraints`
+WHERE+`Constraint_Type`+=+`'P'`
+++AND+`Owner`+=+`'your+schema+here';`|code-block|syntax|javascript|1541405|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you need it in Oracle it is so simple.
<pre><code>SELECT `Constraint_Name`
 FROM `All_Constraints`
 WHERE `Constraint_Type` = `'P'`
 AND `Owner` = `'your schema here';`
</code></pre>

Simple question, how do you list the primary key of a table with T-SQL? I know how to get indexes on a table, but can't remember how to get the PK.

How do you list the primary key of a SQL Server table?

SQL Server

数据库

服务器

简单的问题是，如何使用T-SQL列出表的主键？我知道如何获取表上的索引，但不记得如何获取主键。

问如何列出SQL Server表的主键？
EN

回答 20

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何列出SQL Server表的主键？EN

回答 20

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何列出SQL Server表的主键？
EN