如何使用主键打印出列的“主键”？
如果表具有主键,而不是带有主键的一列和其他列在keyType中为空,则为所有列获取“主键”.
- SELECT c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.Column_default,c.character_maximum_length,c.numeric_precision,c.is_nullable,CASE
- WHEN u.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 'primary key'
- ELSE ''
- END AS KeyType
- FROM INFORMATION_SCHEMA.COLUMNS as c
- LEFT JOIN information_schema.table_constraints as u ON c.table_name = u.table_name
- ORDER BY table_name
解决方法
- SELECT c.TABLE_NAME,CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 'PRIMARY KEY' ELSE '' END AS KeyType
- FROM INFORMATION_SCHEMA.COLUMNS c
- LEFT JOIN (
- SELECT ku.TABLE_CATALOG,ku.TABLE_SCHEMA,ku.TABLE_NAME,ku.COLUMN_NAME
- FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
- INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ku
- ON tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
- AND tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
- ) pk
- ON c.TABLE_CATALOG = pk.TABLE_CATALOG
- AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
- AND c.TABLE_NAME = pk.TABLE_NAME
- AND c.COLUMN_NAME = pk.COLUMN_NAME
- ORDER BY c.TABLE_SCHEMA,c.TABLE_NAME,c.ORDINAL_POSITION
