获取数据库中所有主键的列表
|
这是最好的方法-获取数据库中所有主键的列表-还是有更好的方法？
SELECT
KCU.TABLE_NAME AS Table_Name,
KCU.CONSTRAINT_NAME AS Constraint_Name,
KCU.COLUMN_NAME AS COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA
AND KCU.TABLE_NAME = TC.TABLE_NAME
WHERE
TC.CONSTRAINT_TYPE = \'PRIMARY KEY\'
ORDER BY
KCU.TABLE_SCHEMA, KCU.TABLE_NAME, KCU.CONSTRAINT_NAME
没有找到相关结果
已邀请:
5 个回复
拟蓬
sp_pkeys将为每个返回一行 参与的列 的主键。的 您可能最感兴趣的列 其中有COLUMN_NAME和PK_NAME。 sp_helpconstraint将列出所有 的约束，包括 引用表的外键。 在第一个记录集，将 只能是称为“对象名称”的列 （有点没用，因为那是 您通过了）。在第二 结果集，将有以下内容 列：constraint_type， 约束名称和约束键。土投
USE databasename; GO SELECT i.name AS IndexName, OBJECT_NAME(ic.OBJECT_ID) AS TableName, COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id WHERE i.is_primary_key = 1茶鬼失形
系漏
SELECT so.name \'Table Name\', c.name \'Column Name\', t.Name \'Data type\', c.max_length \'Max Length\', c.precision , c.scale , c.is_nullable, ISNULL(i.is_primary_key, 0) \'Primary Key\' FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id INNER JOIN sysobjects so ON c.object_id = so.id WHERE i.is_primary_key = 1 and so.xtype = \'U\' Order By \'Table Name\', \'Column Name\'穗揣