SQL Server查询以获取表中列的列表以及数据类型，长度，唯一，非空，主键，外键及其引用

2 回答

• 1

  我想你在找

  SELECT C.name ColumnName,
         OBJECT_NAME(C.object_id) TableName,
         OBJECT_NAME(FKC.referenced_object_id) ReferencedTable,
         COL_NAME(FKC.referenced_object_id, FKC.parent_column_id) ReferencedColumn,
         T.name DataType,
         C.max_length,
         C.precision,
         C.collation_name,
         C.is_nullable
         --...
  FROM Sys.Columns C LEFT JOIN sys.foreign_key_columns FKC
           ON C.object_id = FKC.parent_object_id
       JOIN Sys.Types T
           ON C.system_type_id = T.system_type_id
  WHERE C.object_id = OBJECT_ID('YourTableNameHere');
  

  回复于 2024-04-29T11:12:36+08:00
• 1

  我相信你需要这样的东西 . 如果只想查看带有外键的表，则需要将 LEFT JOIN 更改为 INNER JOIN .

  SELECT    t.object_id
          , t.name TableName
          , C.name ColumnName
          , C.column_id ColumnSeq
          , S.name DataType
          , tf.name ParentTableName
          , CF.name ParentColumnName
  FROM sys.tables T
  INNER JOIN sys.columns C ON T.object_id = C.object_id
  INNER JOIN sys.types S ON C.system_type_id = S.system_type_id
  LEFT JOIN sys.foreign_key_columns FSK ON FSK.parent_object_id = t.object_id  AND FSK.parent_column_id = C.column_id
  LEFT JOIN SYS.columns CF ON FSK.referenced_object_id = CF.object_id AND FSK.referenced_column_id = CF.column_id
  LEFT JOIN SYS.tables TF ON cf.object_id = TF.object_id
  

  回复于 2024-04-29T11:12:36+08:00