MYSQL查询表的主键

SELECT

t.TABLE_NAME,

t.CONSTRAINT_TYPE,

c.COLUMN_NAME,

c.ORDINAL_POSITION

FROM

INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c

WHERE

t.TABLE_NAME = c.TABLE_NAME

AND t.TABLE_SCHEMA = 'test'

AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'

测试执行结果如下:

mysql>SELECT

-> t.TABLE_NAME,

-> t.CONSTRAINT_TYPE,

-> c.COLUMN_NAME,

-> c.ORDINAL_POSITION

->FROM

-> INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,

-> INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c

->WHERE

-> t.TABLE_NAME = c.TABLE_NAME

-> AND t.TABLE_SCHEMA = 'test'

-> AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'

->LIMIT 3

+------------+-----------------+-------------+------------------+

| TABLE_NAME | CONSTRAINT_TYPE | COLUMN_NAME | ORDINAL_POSITION |

+------------+-----------------+-------------+------------------+

| mr_dept| PRIMARY KEY | dept_id |1 |

| order | PRIMARY KEY | id |1 |

| tab| PRIMARY KEY | id |1 |

+------------+-----------------+-------------+------------------+

3 rows in set (0.06 sec)

在SYBASE的ASE和ASA中，可以使用如下的系统存储过程来获取主键：

exec

sp_pkeys

'table_name'

sql

server也是一样的！

至于mysql,我没这个环境，相信应该有类似的存储过程的！

---