--查询数据库的所有表
SELECT name,crdate FROM  sysobjects WHERE   xtype = 'U' ORDER BY Name ASC 

--查询指定表的所有字段信息
SELECT  表名 = CASE WHEN C.column_id = 1 THEN O.name  ELSE N'' END ,
        表说明 = ISNULL(CASE WHEN C.column_id = 1 THEN PTB.[value] END, N'') ,
        字段序号 = C.column_id ,
        字段名 = C.name ,
        主键 = ISNULL(IDX.PrimaryKey, N'') ,
        标识 = CASE WHEN C.is_identity = 1 THEN N'1'ELSE N'' END ,
        计算列 = CASE WHEN C.is_computed = 1 THEN N'1'ELSE N''END ,
        类型 = T.name ,
        字节数 = C.max_length ,
        长度 = COLUMNPROPERTY(C.[object_id], C.name, 'precision') ,
        小数位数 = C.scale ,
        允许空 = CASE WHEN C.is_nullable = 1 THEN N'1'ELSE N''END ,
        默认值 = ISNULL(D.definition, N'') ,
        字段说明 = ISNULL(PFD.[value], N'') ,
        索引 = ISNULL(IDX.IndexName, N'') ,
        索引排序 = ISNULL(IDX.Sort, N'') ,
        创建日期 = O.Create_Date ,
        修改日期 = O.Modify_date
FROM    sys.columns C
        INNER JOIN sys.objects O ON C.[object_id] = O.[object_id] AND O.type = 'U' AND O.is_ms_shipped = 0
        INNER JOIN sys.types T ON C.user_type_id = T.user_type_id
        LEFT JOIN sys.default_constraints D ON C.[object_id] = D.parent_object_id AND C.column_id = D.parent_column_id AND C.default_object_id = D.[object_id]
        LEFT JOIN sys.extended_properties PFD ON PFD.class = 1 AND C.[object_id] = PFD.major_id AND C.column_id = PFD.minor_id
        LEFT JOIN sys.extended_properties PTB ON PTB.class = 1 AND PTB.minor_id = 0 AND C.[object_id] = PTB.major_id
        -- 索引及主键信息
        LEFT JOIN 
        ( SELECT    IDXC.[object_id] ,
                    IDXC.column_id ,
                    Sort = CASE INDEXKEY_PROPERTY(IDXC.[object_id],
                                                  IDXC.index_id,
                                                  IDXC.index_column_id,
                                                  'IsDescending')
                             WHEN 1 THEN 'DESC'
                             WHEN 0 THEN 'ASC'
                             ELSE ''
                           END ,
                    PrimaryKey = CASE WHEN IDX.is_primary_key = 1 THEN N'1'ELSE N''END ,
                    IndexName = IDX.Name
          FROM      sys.indexes IDX
                    INNER JOIN sys.index_columns IDXC ON IDX.[object_id] = IDXC.[object_id]AND IDX.index_id = IDXC.index_id
                    LEFT JOIN sys.key_constraints KC ON IDX.[object_id] = KC.[parent_object_id] AND IDX.index_id = KC.unique_index_id
                    INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息
                    ( SELECT    [object_id] ,
                                Column_id ,
                                index_id = MIN(index_id)
                      FROM      sys.index_columns
                      GROUP BY  [object_id] ,
                                Column_id
                    ) IDXCUQ ON IDXC.[object_id] = IDXCUQ.[object_id]
                                AND IDXC.Column_id = IDXCUQ.Column_id
                                AND IDXC.index_id = IDXCUQ.index_id
        ) IDX ON C.[object_id] = IDX.[object_id]AND C.column_id = IDX.column_id 
-- WHERE O.name=N'要查询的表'       -- 如果只查询指定表,加上此条件
ORDER BY O.name ,C.column_id