查看所有表的主键名，主键列

 

/************************************************************
 * Code formatted by SoftTree SQL Assistant ?v6.5.278
 * Time: 2014/7/31 14:25:04
 ************************************************************/



SELECT sysobjects.name,
       syscons.name,
       syscolumns.name
FROM   sysobjects
       INNER JOIN syscolumns
            ON  sysobjects.id = syscolumns.id
       LEFT OUTER JOIN (
                SELECT o.name     sTableName,
                       c.Name     sColName
                FROM   sysobjects o
                       INNER JOIN sysindexes i
                            ON  o.id = i.id
                            AND (i.status & 0X800) = 0X800
                       INNER JOIN syscolumns c1
                            ON  c1.colid <= i.keycnt
                            AND c1.id = o.id
                       INNER JOIN syscolumns c
                            ON  o.id = c.id
                            AND c.name = INDEX_COL(o.name, i.indid, c1.colid)
            ) pkElements
            ON  pkElements.sTableName = sysobjects.name
            AND pkElements.sColName = syscolumns.name
       INNER JOIN sysobjects syscons
            ON  sysobjects.id = syscons.parent_obj
            AND syscons.xtype = 'PK'
WHERE  sTableName IS NOT NULL
ORDER BY
       --Case (syscolumns.Status & 128) when 128 then 1 else 0 end DESC,   
       sysobjects.name 
       
       
SELECT *
FROM   INFORMATION_SCHEMA.TABLES t
WHERE  EXISTS (
           SELECT sysobjects.name,
                  syscons.name,
                  syscolumns.name
           FROM   sysobjects
                  INNER JOIN syscolumns
                       ON  sysobjects.id = syscolumns.id
                  LEFT OUTER JOIN (
                           SELECT o.name     sTableName,
                                  c.Name     sColName
                           FROM   sysobjects o
                                  INNER JOIN sysindexes i
                                       ON  o.id = i.id
                                       AND (i.status & 0X800) = 0X800
                                  INNER JOIN syscolumns c1
                                       ON  c1.colid <= i.keycnt
                                       AND c1.id = o.id
                                  INNER JOIN syscolumns c
                                       ON  o.id = c.id
                                       AND c.name = INDEX_COL(o.name, i.indid, c1.colid)
                       ) pkElements
                       ON  pkElements.sTableName = sysobjects.name
                       AND pkElements.sColName = syscolumns.name
                  INNER JOIN sysobjects syscons
                       ON  sysobjects.id = syscons.parent_obj
                       AND syscons.xtype = 'PK'
           WHERE  sysobjects.name = t.TABLE_NAME
                  AND sTableName IS NOT NULL
       )