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('Sys_Member')
引用自:https://www.cnblogs.com/zix1314/p/8294846.html
