取得SQL Server 2005各表、字段的说明等信息的SQL语句[转]

 SELECT TableName =D.NAME,
       TableComment = CASE
         WHEN A.COLORDER = 1 THEN
          ISNULL(F.VALUE, ' ')
         ELSE
          ' '
       END,
       ColumnIndex = A.COLORDER,
       ColumnName = A.NAME,
       ColumnIdentity = CASE
         WHEN COLUMNPROPERTY(A.ID, A.NAME, 'ISIDENTITY ') = 1 THEN
          '1 '
         ELSE
          '0'
       END,
       PrimaryKey = 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
          '1'
         ELSE
          '0'
       END,
       ColumnType = B.NAME,
       ColumnBit = A.LENGTH,
       ColumnLength = COLUMNPROPERTY(A.ID, A.NAME, 'PRECISION '),
       ColumnDecimal = ISNULL(COLUMNPROPERTY(A.ID, A.NAME, 'SCALE '), 0),
       ColumnNullAble = CASE
         WHEN A.ISNULLABLE = 1 THEN
          '1'
         ELSE
          '0'
       END,
       DefaultValue = ISNULL(E.TEXT, ' '),
       ColumnComment = 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 ='TABLE_NAME' 
 ORDER BY A.ID, A.COLORDER
posted @ 2011-05-11 15:54  hinsxun  阅读(247)  评论(0编辑  收藏  举报