首页 > 代码库 > 取得MSSQL表中字段及主键等属性SQL语法

取得MSSQL表中字段及主键等属性SQL语法

SELECT  c.NAME AS [Column Name], t.NAME AS [Data type],        c.max_length AS [Max Length], c.precision, c.scale,        c.is_nullable AS [Is Nullable], c.is_identity AS [Is Identity],        ISNULL(i.is_primary_key, 0) [Primary Key], i.type_desc,        OBJECT_SCHEMA_NAME(fk.object_id) + . + OBJECT_NAME(fk.object_id)        +  ( + fk.Name + ) AS [Foreign Key]FROM    sys.columns c        INNER JOIN sys.types t ON c.system_type_id = t.system_type_id                                  AND c.user_type_id = t.user_type_id        LEFT OUTER JOIN ( sys.index_columns ic                          INNER JOIN sys.indexes i ON ic.object_id = i.object_id                                                      AND i.is_primary_key = 1                                                      AND ic.index_id = i.index_id                        ) ON ic.object_id = c.object_id                             AND ic.column_id = c.column_id        LEFT JOIN sys.foreign_key_columns fkc ON fkc.parent_object_id = c.object_id                                                 AND fkc.parent_column_id = c.column_id        LEFT JOIN sys.columns fk ON fk.object_id = fkc.referenced_object_id                                    AND fk.column_id = fkc.referenced_column_idWHERE   c.object_id = OBJECT_ID(Table Name)ORDER BY c.Column_ID ;

 

取得MSSQL表中字段及主键等属性SQL语法