取得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_id
WHERE   c.object_id = OBJECT_ID('Table Name')
ORDER BY c.Column_ID ;

 

转载于:https://www.cnblogs.com/ywkpl/p/4185279.html