sql问题

[qq_36521727]

sql通过主键名称怎么查询所在表中列名称？？

[唐诗三百首]

请看这3个系统表: sys.key_constraints, sys.columns, sys.tables.

[听雨停了]

--通过主键名找表的对应列名
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 行受影响)

[吉普赛的歌]

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('表名')