-- =============================================

-- Author: gengc

-- Create date: <2012-12-29>

-- Description: <查看表结构>

-- =============================================

CREATE View ViewTable

as

select

obj.name as 'TableName'

,c.name as '字段名称'

,isnull(etp.value,'') AS '字段描述'

,t.name as '字段类型'

,c.Length as '占用字节'

,ColumnProperty(c.id,c.name,'PRECISION') as '长度'

,isnull(ColumnProperty(c.id,c.name,'Scale'),0) as '小数位数'

,case(c.isnullable) when '1' then '√' else '' end as '是否为空'

,ISNULL(cm.text,'') as '默认值'

,case(

(select 1 from sysobjects where xtype='PK' and parent_obj=c.id and name in (

select name from sysindexes where indid in(

select indid from sysindexkeys where id = c.id and colid=c.colid)))

) when '1' then '√' else '' end as '是否主键'

,case(ColumnProperty(c.id,c.name,'IsIdentity')) when '1' then '√' else '' end as '自动增长'

from syscolumns c

inner join systypes t on c.xusertype = t.xusertype

left join sys.extended_properties etp on etp.major_id = c.id and etp.minor_id = c.colid and etp.name ='MS_Description'

left join syscomments cm on c.cdefault=cm.id

left join sysobjects obj on c.id=obj.id

 

================================================

 

select [Name],[Text]

  from syscomments A

   join sysobjects B on A.id=B.id

where [Name]='ViweName'