查询所有用户表和表的结构

--查询所有用户表
SELECT  name
FROM    sysobjects
WHERE   xtype = 'U'

--查询所有用户表的表名、表结构、字段注释
SELECT  表名 = CASE WHEN A.COLORDER = 1 THEN D.NAME
                  ELSE ' '
             END ,
        表备注 = CASE WHEN A.COLORDER = 1 THEN ISNULL(F.VALUE, ' ')
                   ELSE ' '
              END ,
        列序号 = A.COLORDER ,
        列名称 = A.NAME ,
        标识 = CASE WHEN COLUMNPROPERTY(A.ID, A.NAME, 'ISIDENTITY ') = 1
                  THEN '√ '
                  ELSE ' '
             END ,
        主键 = CASE WHEN EXISTS ( SELECT  1
                                FROM    SYSOBJECTS
                                WHERE   XTYPE = 'PK '
                                        AND PARENT_OBJ = A.ID
                                        AND NAME IN (
                                        SELECT  NAME
                                        FROM    SYSINDEXES
                                        WHERE   INDID IN (
                                                SELECT  INDID
                                                FROM    SYSINDEXKEYS
                                                WHERE   ID = A.ID
                                                        AND COLID = A.COLID ) ) )
                  THEN '√ '
                  ELSE ' '
             END ,
        类型 = B.NAME ,
        字节 = A.LENGTH ,
        长度 = COLUMNPROPERTY(A.ID, A.NAME, 'PRECISION ') ,
        小数位 = ISNULL(COLUMNPROPERTY(A.ID, A.NAME, 'SCALE '), 0) ,
        允许空 = CASE WHEN A.ISNULLABLE = 1 THEN '√ '
                   ELSE ' '
              END ,
        默认值 = ISNULL(E.TEXT, ' ') ,
        列备注 = ISNULL(G.[VALUE], ' ')
FROM    SYSCOLUMNS A
        LEFT   JOIN SYSTYPES B ON A.XUSERTYPE = B.XUSERTYPE
        INNER   JOIN SYSOBJECTS D ON A.ID = D.ID
                                     AND D.XTYPE = 'U '
                                     AND D.NAME <> 'DTPROPERTIES '
        LEFT   JOIN SYSCOMMENTS E ON A.CDEFAULT = E.ID
        LEFT   JOIN sys.extended_properties G ON A.ID = G.major_id
                                                 AND A.COLID = G.minor_id
        LEFT   JOIN sys.extended_properties F ON D.ID = F.major_id
                                                 AND F.minor_id = 0
--where  D.NAME='sys_Area'   --查询这个表
ORDER BY A.ID ,
        A.COLORDER