摘自：http://blog.csdn.net/keenweiwei/article/details/6859490
1 SELECT 2 C.name as [字段名],T.name as [字段类型] 3 ,convert(bit,C.IsNullable) as [可否为空] 4 ,convert(bit,case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=c.id and name in ( 5 SELECT name FROM sysindexes WHERE indid in( 6 SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) then 1 else 0 end) 7 as [是否主键] 8 ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as [自动增长] 9 ,C.Length as [占用字节] 10 ,COLUMNPROPERTY(C.id,C.name,'PRECISION') as [长度] 11 ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as [小数位数] 12 ,ISNULL(CM.text,'') as [默认值] 13 ,isnull(ETP.value,'') AS [字段描述] 14 --,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row] 15 FROM syscolumns C 16 INNER JOIN systypes T ON C.xusertype = T.xusertype 17 left JOIN sys.extended_properties ETP ON ETP.major_id = c.id AND ETP.minor_id = C.colid AND ETP.name ='MS_Description' 18 left join syscomments CM on C.cdefault=CM.id 19 WHERE C.id = object_id('tblAddressBook')
(运行环境：sql server 2008) 以上是中文作为列名，以下是英文作为列名，注意: 把 tblAddressBook 换成你要查询的表名就OK了
1 SELECT 2 C.name as FieldName,T.name as TypeName 3 ,C.IsNullable 4 ,case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=c.id and name in ( 5 SELECT name FROM sysindexes WHERE indid in( 6 SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) then 1 else 0 end 7 as IsPrimary 8 ,COLUMNPROPERTY(c.id,c.name,'IsIdentity') as IsIdentity 9 ,C.Length as ByteLength 10 ,COLUMNPROPERTY(C.id,C.name,'PRECISION') as StringLength 11 ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as DotPrecision 12 ,ISNULL(CM.text,'') as DefaultValue 13 ,isnull(ETP.value,'') AS [Description] 14 --,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row] 15 FROM syscolumns C 16 INNER JOIN systypes T ON C.xusertype = T.xusertype 17 left JOIN sys.extended_properties ETP ON ETP.major_id = c.id AND ETP.minor_id = C.colid AND ETP.name ='MS_Description' 18 left join syscomments CM on C.cdefault=CM.id 19 WHERE C.id = object_id('tblAddressBook')
附查询图片一张：
