sql-server – 信息架构和主键
发布时间:2021-01-14 22:43:08 所属栏目:MsSql教程 来源:网络整理
导读:如何使用主键打印出列的“主键”? 如果表具有主键,而不是带有主键的一列和其他列在keyType中为空,则为所有列获
如何使用主键打印出列的“主键”? 如果表具有主键,而不是带有主键的一列和其他列在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 (编辑:揭阳站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |