孤独的猫

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::
摘自: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')

        
附查询图片一张:

posted on 2012-11-30 15:29  孤独的猫  阅读(2589)  评论(0编辑  收藏  举报