SQLSERVER以视图的方式查询表结构和视图结构

-- ============================================= -- Author: gengc

-- Create date: <2012-12-29> -- Description: <查看表结构>

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

CREATE View ViewTableasselect

obj.name as 'TableName',f.value as 'TableDesc',c.name as 'CName'

,isnull(etp.value,'') AS 'CDesc',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 'Y' 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 'Y' else '' end as '是否主键'

,case(ColumnProperty(c.id,c.name,'IsIdentity')) when '1' then 'Y' 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.idleft join sysobjects obj on c.id=obj.id

left join sys.extended_properties f on obj.id=f.major_id and f.minor_id=0where obj.type='U'

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

select [Name],[Text] from syscomments A

join sysobjects B on A.id=B.idwhere [Name]='ViweName'