/****** Object: View [dbo].[ViewTable] Script Date: 09/06/2016 11:12:28 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author: gengc

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

-- Description: <查看表结构>

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

CREATE VIEW [dbo].[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

GO