信息模式和主键

Question

如何仅打印具有主键的列的“主键”？

如果表具有主键，则会为所有列获取“主键”，而不是将具有主键的一列和其他列作为空白的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

- cdub

---

可能是重复的问题：如何确定SQL Server表的主键？ - Conrad Frix

Answer 1

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 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 

- EricZ

---

非常出色的工作，这正是我想要的...真希望我能给你更多的分数。:o) - cdub

tc JOIN ku is missing ON ... AND tc.CONSTRAINT_CATALOG = ku.CONSTRAINT_CATALOG AND tc.CONSTRAINT_SCHEMA = ku.CONSTRAINT_SCHEMAtc JOIN ku 缺少 ON ... AND tc.CONSTRAINT_CATALOG = ku.CONSTRAINT_CATALOG AND tc.CONSTRAINT_SCHEMA = ku.CONSTRAINT_SCHEMA - user482745

Answer 2

另外，考虑查看以下内容：

sp_helptext N'sp_help'

有时从微软团队学习是一件很棒的事情。:-)

- Gregory A Beamer

Answer 3

第一个答案中还有一个非常小的补充。可以同时查看MS_Description（或其他扩展属性）。

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 pk.COLUMN_NAME IS NOT NULL THEN 'PRIMARY KEY' ELSE '' END AS KeyType, c.ORDINAL_POSITION 
             ,convert(varchar(8000), ex.value) as coln
   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
    outer apply ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', c.TABLE_NAME, 'column', c.COLUMN_NAME) ex
   ORDER BY c.TABLE_SCHEMA,c.TABLE_NAME, c.ORDINAL_POSITION 

- marcS

Answer 4

我发现以下代码是查找表的主键最简单的方法。它只需要在information_schema.columns表和information_schema.key_column_usage表之间进行左外连接。

Select 
  col.column_name, 
  col.data_type, 
  col.ordinal_position, 
  col.is_nullable, 
  case when ky.COLUMN_NAME is null then 0 else 1 end as primary_key_flag
from <your-catalog>.Information_Schema.columns col
  left outer join <your-catalog>.INFORMATION_SCHEMA.KEY_COLUMN_USAGE ky
    on ky.COLUMN_NAME = col.COLUMN_NAME and ky.TABLE_NAME = col.TABLE_NAME 
      and ky.TABLE_CATALOG = col.TABLE_CATALOG and ky.table_name = col.table_name
where 
  col.TABLE_NAME = '<your-table-name>' 
  and col.table_catalog = '<your-catalog>';

注意：此代码是使用SQL Server 2017 Express (14.0.1000)构建的，在创建主键后，我能够在KEY_COLUMN_USAGE表中看到这些值。我还在CONSTRAINT_TABLE_USAGE中看到了一个标识constraint_name的记录，但我并没有发现这是获取连接所需的必要条件，以显示主键。

最后说明：我在个人项目中重新开始使用SQL Server，距离上次使用数据库（使用MySQL）已经过去将近十年，因此我远非该领域的专家。

- bigdavey71