--查询用户表对象信息
SELECT Tab.Name                         AS [表名],
       Tab.create_date                  AS [创建时间],
       Tab.modify_date                  AS [最后修改时间],
       Col.Name                         AS [列名],
       Type.name                        AS [数据类型],
       Col.max_length                   AS [字段长度],
       CASE
         WHEN pk.is_primary_key = 1 THEN 'Y'
         ELSE 'N'
       END                              AS [是否主键],
       CASE
         WHEN Col.is_identity = 1 THEN 'Y'
         ELSE 'N'
       END                              AS [是否自增],
       identity_columns.seed_value      AS [自增种子],
       identity_columns.increment_value AS [自增步长],
       CASE
         WHEN Col.is_nullable = 1 THEN 'Y'
         ELSE 'N'
       END                              AS [是否允许为NULL],
       Def.text                         AS [默认值],
       CASE
         WHEN Col.is_computed = 1 THEN 'Y'
         ELSE 'N'
       END                              AS [是否计算列],
       computed_columns.definition      AS [计算公式],
       Col_Desc.Value                   AS [列备注]
FROM   sys.objects Tab
       INNER JOIN sys.columns Col
         ON Tab.object_id = Col.object_id
       INNER JOIN sys.types Type
         ON Col.system_type_id = Type.system_type_id
       LEFT JOIN sys.identity_columns identity_columns
         ON Tab.object_id = identity_columns.object_id
            AND Col.column_id = identity_columns.column_id
       LEFT JOIN syscomments Def
         ON Col.default_object_id = Def.ID
       LEFT JOIN(SELECT index_columns.object_id,
                        index_columns.column_id,
                        indexes.is_primary_key
                 FROM   sys.indexes indexes
                        INNER JOIN sys.index_columns index_columns
                          ON indexes.object_id = index_columns.object_id
                             AND indexes.index_id = index_columns.index_id
                 WHERE  indexes.is_primary_key = 1/*主键*/
                ) PK
         ON Tab.object_id = PK.object_id
            AND Col.column_id = PK.column_id
       LEFT JOIN sys.computed_columns computed_columns
         ON Tab.object_id = computed_columns.object_id
            AND Col.column_id = computed_columns.column_id
       LEFT JOIN sys.extended_properties Col_Desc
         ON Col_Desc.major_id = Tab.object_id
            AND Col_Desc.minor_id = Col.Column_id
            AND Col_Desc.class = 1
WHERE  Tab.type = 'U'
       AND Tab.Name NOT LIKE'sys%'
ORDER  BY Tab.create_date