取得sql2005 表字段信息的sql语句

 

SELECT     ROW_NUMBER() OVER (ORDER BY id) AS ROW_NUMBER, FieldName, fieldDescn AS cnName, FieldType, fieldLength, isIdentifier, iskeyField, allowNull, defaultValue, fieldDescnFROM         (SELECT     TOP 100 a.id, d .name N'TableName', a.colorder N'FieldNumber', a.name N'FieldName', (CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity')                                               = 1 THEN '○' ELSE ' ' END) N'IsIdentifier', (CASE WHEN                                                  (SELECT     count(*)                                                    FROM          sysobjects                                                    WHERE      (name IN                                                                               (SELECT     name                                                                                 FROM          sysindexes                                                                                 WHERE      (id = a.id) AND (indid IN                                                                                                            (SELECT     indid                                                                                                              FROM          sysindexkeys                                                                                                              WHERE      (id = a.id) AND (colid IN                                                                                                                                         (SELECT     colid                                                                                                                                           FROM          syscolumns                                                                                                                                           WHERE      (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK')) > 0 THEN '○' ELSE ' ' END) N'IsKeyField',                                               b.name N'FieldType', a.length N'FieldSize', COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS N'FieldLength', isnull(COLUMNPROPERTY(a.id, a.name,                                               'Scale'), 0) AS N'DecimalDigits', (CASE WHEN a.isnullable = 1 THEN ' ' ELSE '○' END) N'AllowNull', isnull(e.text, '') N'DefaultValue', isnull(g.[value], '')                                               AS N'FieldDescn'                       FROM          syscolumns a LEFT JOIN                                              systypes b ON a.xtype = 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                       ORDER BY object_name(a.id), a.colorder) ttt