/************************************************************
* 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
)
查看所有表的主键名,主键列
最新推荐文章于 2022-12-01 09:06:14 发布