34,593
社区成员
发帖
与我相关
我的任务
分享
SELECT t.name AS tableName,
i.name AS pkName,
STUFF((SELECT ',' + c.name
FROM sys.sysindexkeys AS s2
INNER JOIN sys.[columns] AS c
ON c.column_id = s2.colid
AND c.[object_id] = t.[object_id]
AND s2.indid = i.index_id
AND s2.id = t.[object_id] FOR XML PATH('')
),1,1,'' ) AS pkColumnNames
FROM sys.tables AS t
INNER JOIN sys.indexes i
ON t.[object_id] = i.[object_id]
INNER JOIN sys.stats s
ON i.object_id = s.object_id
AND i.index_id = s.stats_id
AND i.is_primary_key = 1
--WHERE t.[object_id]=OBJECT_ID('表名')
--通过主键名找表的对应列名
SELECT sys.key_constraints.name,object_name(parent_object_id) AS tableName,SYSCOLUMNS.name
FROM SYSCOLUMNS,SYSOBJECTS,SYSINDEXES,SYSINDEXKEYS,sys.key_constraints
WHERE sys.key_constraints.name = 'PK__USER_mst__3213E83F7F60ED59' --sys.key_constraints.name为主键名称
AND SYSCOLUMNS.id=sys.key_constraints.parent_object_id
AND SYSOBJECTS.xtype = 'PK' --sysobjects.xtype对象类型
AND SYSOBJECTS.parent_obj = SYSCOLUMNS.id
AND SYSINDEXES.id = SYSCOLUMNS.id
AND SYSOBJECTS.name = SYSINDEXES.name
AND SYSINDEXKEYS.id = SYSCOLUMNS.id
AND SYSINDEXKEYS.indid = SYSINDEXES.indid
AND SYSCOLUMNS.colid = SYSINDEXKEYS.colid
name tableName name
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
PK__USER_mst__3213E83F7F60ED59 USER_mstr id
(1 行受影响)
--通过表找主键名和对应的列名
SELECT object_name(parent_object_id) AS tableName,sys.key_constraints.name,SYSCOLUMNS.name
FROM SYSCOLUMNS,SYSOBJECTS,SYSINDEXES,SYSINDEXKEYS,sys.key_constraints
WHERE SYSCOLUMNS.id = object_id('user_mstr') --syscolumns.id为该列所属的表对象ID
AND SYSCOLUMNS.id=sys.key_constraints.parent_object_id
AND SYSOBJECTS.xtype = 'PK' --sysobjects.xtype对象类型
AND SYSOBJECTS.parent_obj = SYSCOLUMNS.id
AND SYSINDEXES.id = SYSCOLUMNS.id
AND SYSOBJECTS.name = SYSINDEXES.name
AND SYSINDEXKEYS.id = SYSCOLUMNS.id
AND SYSINDEXKEYS.indid = SYSINDEXES.indid
AND SYSCOLUMNS.colid = SYSINDEXKEYS.colid
tableName name name
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
USER_mstr PK__USER_mst__3213E83F7F60ED59 id
(1 行受影响)